Tracking Which Sheet is Unprotected

[Abhijeet]

Hi

I have Day Wise Sheets & prior to Today's date all sheets are protected with help of Macro

But few users unlock these sheets so i want to track if prior to Today's date if any sheet is Unprotected that sheet name & time

Please tell me is this possible to track

 

[vletm]

You can use this
or add this to make date/time stamp somewhere...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With ActiveSheet
        If Not .ProtectContents Then .Protect
    End With
End Sub

 

[Abhijeet]

This is only changes in sheet then protect the sheet i want to prior to today date if sheet password unlock then want to know sheet name & Date and time

 

[vletm]

... and still You cannot read second line!

 

[vletm]

How about this longer version?

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    i_tab = "ilkimykset"
    Sheets(i_tab).Select
    If Err.Number <> 0 Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = i_tab
        Sheets(i_tab).Visible = xlSheetVeryHidden
    End If
    With Sheets(i_tab)
        .Unprotect
        .Range("A:B").ClearContents
        For Sh = 1 To Worksheets.Count
            .Cells(Sh, 1) = Sheets(Sh).Name
            .Cells(Sh, 2) = Sheets(Sh).ProtectContents
        Next Sh
        .Visible = xlSheetVeryHidden
        .Protect
    End With
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With ActiveSheet
        PC = .ProtectContents
        i_tab = "ilkimykset"
        asn = .Name
        With Sheets(i_tab)
            y = 0
            Do
                y = y + 1
            Loop Until .Cells(y, 1) = asn Or .Cells(y, 1) = Empty
            If .Cells(y, 1) = Empty Then .Cells(y, 1) = Ssh
            If .Cells(y, 2) <> PC Then
                .Unprotect
                .Cells(y, 2) = PC
                yc = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
                .Cells(yc, 3) = Now
                .Cells(yc, 3).NumberFormat = "dd/mm/yy hh:mm:ss"
                .Cells(yc, 4) = Application.UserName
                .Cells(yc, 5) = asn
                .Cells(yc, 6) = PC
                .Cells(yc, 7) = Target.Address
                .Protect
                ThisWorkbook.Save
            End If
        End With
    End With
    Application.EnableEvents = True
End Sub

Private Sub Workbook_Activate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With ActiveSheet
        PC = .ProtectContents
        i_tab = "ilkimykset"
        asn = .Name
        With Sheets(i_tab)
            y = 0
            Do
                y = y + 1
            Loop Until .Cells(y, 1) = asn Or .Cells(y, 1) = Empty
            If .Cells(y, 1) = Empty Then .Cells(y, 1) = Ssh
            If .Cells(y, 2) <> PC Then
                .Unprotect
                .Cells(y, 2) = PC
                yc = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
                .Cells(yc, 3) = Now
                .Cells(yc, 3).NumberFormat = "dd/mm/yy hh:mm:ss"
                .Cells(yc, 4) = Application.UserName
                .Cells(yc, 5) = asn
                .Cells(yc, 6) = PC
                .Protect
                ThisWorkbook.Save
            End If
        End With
    End With
    Application.EnableEvents = True
End Sub

 

[Abhijeet]

Hi
Thanks for giving code but this macro not give any protected sheet unprotected then give that sheet name & date & time

 

[vletm]

Are Your all sheet really fully locked?
If none range cannot write ... are those only for view?
if someone really really NEED to update something
then some active have to unprotect those sheets!
Do Your tests with file which even have something common with Your real files!

 

[Abhijeet]

Day wise sheet so prior to current date all sheets are lock means 1 to 9 all sheets are locked

 

[vletm]

Abhijeet
Do we write about Weather or about Excel?
You sent 'sample file'.
There are three (3) sheets + 'tracking' sheet.
Sheet1 is protected with password.
Sheet2 isn't protected and ALL CELL are LOCKED.
Sheet3 isn't protected and ALL CELL are LOCKED.
'Tracking' sheet shows 'protection' in B-column also
changes of protections will make stamps to 'tracking'-sheet
if they make any changes after that.
> What is connection of that Your 'test'-file and Your 'real life file'?
> You wrote that all sheets are PROTECTED by default.
> If PROTECTED and FULLY LOCKED => NOBODY cannot edit nothing!
> What is connection Your previous reply of this case?

 

[Abhijeet]

I told you if sheet protected is unprotected then want to know date & time of that sheet name all sheets not protected i already told you

 

[vletm]

It works so!
as I have written many times
it makes stamp AFTER any changes!
It cannot make stamp BEFORE anything!
Maybe You haven't test it at all!
Maybe You cannot make Your test file to same situation with Your real file?
If You test file is so so so different than real file then You cannot test it well.

 

[Abhijeet]

tell me what is this macro give result

 

[vletm]

'What is this? ...
and of course You missed to send 'this' ...?
But this would tell everything about 'tracking'-sheet
A-col = All sheet names
B-col = opening state of protection and after that active state of protection
C-col = timestamp of tracking
D-col = UserName
E-col = Sheet
F-col = Protection state
G-col = cell which activate tracking