How to tell if someone unprotected a worksheet

[anthonydib29]

I have an excel workbook where I’ve locked certain cells within each tab. This workbook will be disseminated to a large group of people, so I’m the event they need to edit a formula or any other locked cell, I’d like them to be able to do so by inserting the password. But I also need to know that they unprotected the sheet, so I can be aware that locked cells within the workbook have been potentially edited. Is there a way to determine if someone has unprotected the sheet/s?

 

[JLGWhiz]

Not an easy way if they re-protected the sheet afterward. But it is possible to use some event code to record a user making changes in locked cells. The problem with that is if the cells are not in a contiguous range of cells, it might require some complex code writing to cover the cells you want to put a check on. It might be easier to just have a macro that you can run and check if the cells you are concerned with still hold the formulas or values you expect them to have.

Take a look here: Determining When An Excel Spreadsheet Is Protected — The Spreadsheet Guru

 

[Jaafar Tribak]

There are no events to capture the user Protecting\UnProtecting worksheets but you could fake them using a workaround as follows :

Add a new log worksheet to your workbook and give it the name of LogSheet. This sheet will be for longging the Protect\UnProtect info. (Better if you make this log sheet hidden or VeryHidden)

The example below assumes the display name of the target worksheet is Sheet1.

Note: You can easily change the target sheet name as well as the Log Sheet name in the two Module-level Constants
TARGET_SHEET_NAME and LOG_SHEET_NAME


Code goes in the ThisWorkbook Module:

Option Explicit

Private Enum PROTECTION_STATUS
    Protected = 0
    UnProtected = 1
End Enum

Private WithEvents Cmbrs As CommandBars

Private Const TARGET_SHEET_NAME = "Sheet1"   '<= change target sheet name as required
Private Const LOG_SHEET_NAME = "LogSheet"      '<= change log sheet name as required


'__________________________________  Monitoring Protection SetUp ________________________________________________

Private Sub Workbook_Activate()
    EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME)) = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME)) = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        EnableSheetProtectionMonitoring(Worksheets(TARGET_SHEET_NAME)) = False
End Sub


'__________________________________  PSEUDO-EVENTS ________________________________________________

Private Sub OnSheetProtect(ByVal Sht As Worksheet)

    LogInfo Status:=Protected, SaveInfoToDisk:=True

End Sub

Private Sub OnSheetUnProtect(ByVal Sht As Worksheet)
   
    LogInfo Status:=UnProtected, SaveInfoToDisk:=True

End Sub


'__________________________________ Helper Routines ________________________________________________

Private Sub LogInfo(ByVal Status As PROTECTION_STATUS, ByVal SaveInfoToDisk As Boolean)

    With Sheets(LOG_SHEET_NAME)
        .Cells(1, 1) = "Protection Status"
        .Cells(1, 2) = "User Name"
        .Cells(1, 3) = "Time Stamp"
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(1) = IIf(Status = Protected, "Sheet Protected", "Sheet Unprotected")
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 1) = Environ("UserName")
        .Cells(.Cells.Rows.Count, 1).End(xlUp).Offset(, 2) = Format(Date, "Short Date") & " @ " & Format(Time, "Long Time")
        .Columns("A:D").EntireColumn.AutoFit
        .Range("A1:D1").Font.Bold = True
    End With
   
    If SaveInfoToDisk Then Me.Save

End Sub

Private Property Let EnableSheetProtectionMonitoring(ByVal Sht As Worksheet, ByVal Enable As Boolean)

    If Enable Then
        Set Cmbrs = Application.CommandBars
    Else
        Set Cmbrs = Nothing
    End If

End Property

Private Sub Cmbrs_OnUpdate()

    Static bPrevEnableState As Boolean
    Dim bCurrentEnableState As Boolean
   
    If ActiveSheet Is Worksheets(TARGET_SHEET_NAME) Then
        bCurrentEnableState = Application.CommandBars.GetEnabledMso("Spelling")
        If bCurrentEnableState And (bCurrentEnableState = Not bPrevEnableState) Then
            Call OnSheetUnProtect(ActiveSheet)
        End If
        If bCurrentEnableState = False And (bCurrentEnableState = Not bPrevEnableState) Then
            Call OnSheetProtect(ActiveSheet)
        End If
        bPrevEnableState = Application.CommandBars.GetEnabledMso("Spelling")
    End If

End Sub

 

[YasserKhalil]

Thanks a lot my friend Jaafar
Here's another workaround
You can create a hidden named range with the original password like that ( this code will be run for just once)

Sub Create_Hidden_Named_Range_If_Not_Exists()
    Dim sName As String, sPass As String
    Rem Change Hidden Named Range To Suit
    sName = "Sheet1Pass"
    Rem Change Worksheet Password To Suit
    sPass = "123"
    If Not IsError(Evaluate(sName)) Then
        MsgBox "Named Range Exists", vbExclamation
    Else
        ThisWorkbook.Names.Add Name:=sName, RefersToR1C1:=sPass, Visible:=False
    End If
End Sub

Then you can use the following code later to check if the worksheet password changed or not

Sub Check_If_Worksheet_Password_Changed()
    Dim ws As Worksheet, sPass As String
    Rem Change Worksheet Name To Suit
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Rem Hidden Named Range As In The First Code
    sPass = Replace(Replace(ThisWorkbook.Names("Sheet1Pass").Value, "=", ""), Chr(34), "")
    If ws.ProtectContents = True Then
        On Error Resume Next
            ws.Unprotect ""
            If Err = 0 Then MsgBox "Worksheet : " & ws.Name & " Was Protected With Empty Pass", vbExclamation: ws.Protect: Exit Sub
        On Error GoTo 0
        On Error Resume Next
            ws.Unprotect sPass
            If Err <> 0 Then
                MsgBox "Password Changed For The Worksheet: " & ws.Name, vbExclamation: Err = 0
            Else
                ws.Protect sPass
                MsgBox "Congratulations! No Change In Worksheet: " & ws.Name, vbInformation
            End If
        On Error GoTo 0
    Else
        MsgBox "No Password For The Worksheet: " & ws.Name, vbExclamation
    End If
End Sub

 

[Jaafar Tribak]

Hi YasserKhalil

I think the OP wants to know If the user unprotected the sheet, not if the user changed the sheet protection password.