Excel VBA没有可检测工作表是否处于受保护/未受保护状态的事件。

不要开枪。

谷歌会把您放在这里https://www.ozgrid.com/forum/index.php?thread/43816-unprotect-worksheet-event/，作者甚至给您提供了一个示例： https://www.ozgrid.com/forum/core/index.php?attachment/1082834-52719-xls/

这不是100％的傻瓜，因为事件处理程序无法告诉用户何时取消保护/取消保护对话框。

此工作簿

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    StartEventListiner False

End Sub

Private Sub Workbook_Open()

    StartEventListiner True

End Sub

模块

Option Explicit
Public g_clsEvnt As CProtectEvt

Public Sub StartEventListiner(Action As Boolean)

    If Action Then
        Set g_clsEvnt = New CProtectEvt
    Else
        Set g_clsEvnt = Nothing
    End If

End Sub

课程

Option Explicit

Public WithEvents cbbProtect As CommandBarButton

Private Sub m_ProtectControls(State As Boolean)

    Dim objX As OLEObject

    On Error Resume Next
    For Each objX In ActiveSheet.OLEObjects
        objX.Object.Enabled = State
    Next

End Sub

Private Sub cbbProtect_Click(ByVal Ctrl As Office.CommandBarButton,CancelDefault As Boolean)

    m_ProtectControls (InStr(1,Ctrl.Caption,"Un&protect",vbTextCompare) > 0)

End Sub

Private Sub Class_Initialize()

    On Error Resume Next

    ' hook into Tools > Protection > Protect Sheet event
    Set cbbProtect = Application.CommandBars.FindControl(msoControlButton,ID:=893)

End Sub

切换和记录工作表保护

• 该代码仅在使用按钮（为其分配了toggleWorksheetProtection_Click或从toggleWorksheetProtection_Click运行VBE时才记录保护。
• 将完整的代码复制到标准模块中（例如Module11）。
• 调整五个 const 蚂蚁的值。
• ThisWorkbook是指包含此代码的工作簿。
• 另外调整writeLogRow中的日期格式。

代码

Option Explicit

Sub toggleWorksheetProtection_Click()
    ' Constants
    Const srcName As String = "Calendar"
    Const tgtName As String = "Log"
    Const tgtCol As Variant = 1
    Const msgProtect As String = "Sheet protected."
    Const msgUnProtect As String = "Sheet unprotected."
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook
    ' Other Variables
    Dim src As Worksheet: Set src = wb.Worksheets(srcName)
    Dim tgt As Worksheet: Set tgt = wb.Worksheets(tgtName)
    Dim msg As String
    ' Protection
    If src.ProtectContents Then
        src.Unprotect: msg = msgUnProtect
    Else
        src.Protect: msg = msgProtect
    End If
    ' Log
    Dim cel As Range
    Set cel = getEmptyCell(tgt,tgtCol)
    Call writeLogRow(cel,msg)

End Sub

Function getEmptyCell(Sheet As Worksheet,ByVal writeColumn As Variant)
    Dim cel As Range
    Set cel = Sheet.Columns(writeColumn).Find("*",xlValues,xlPrevious)
    If Not cel Is Nothing Then
        Set cel = cel.Offset(1)
    Else
        Set cel = Sheet.Cells(1,writeColumn)
    End If
    Set getEmptyCell = cel
End Function

Sub writeLogRow(logRange As Range,ByVal logMessage As String)
    Dim logDate As Date: logDate = Now
    logRange.Value = logDate
    logRange.NumberFormat = "mm/dd/yyyy hh:mm:ss (ddd)"
    logRange.Offset(,1).Value = logMessage
End Sub