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