Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Potential Audit of workbook protection

Copper Contributor

Is it possible to pull a log of changes via the 'Protect - Protect Workbook'?

1 Reply

@Dan_Hockham 

The built-in protection features in Excel, including the 'Protect Workbook' option, do not automatically generate an audit log or history of changes. Excel does not provide a native feature to track changes made specifically through workbook protection.

If you need a detailed audit trail for changes in a workbook, including protection-related changes, you might need to consider alternative approaches:

You could potentially use VBA (Visual Basic for Applications) to create a custom logging mechanism. With VBA, you can capture events like workbook protection status changes and record relevant details in a log.

Here is a simple example to get you started with VBA. This code should be placed in the VBA editor of your workbook:

vbaCode (is untested):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ' Log sheet activation (example)
    LogChange "Sheet Activated: " & Sh.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' Log sheet deactivation (example)
    LogChange "Sheet Deactivated: " & Sh.Name
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' Log sheet changes (example)
    LogChange "Change in " & Sh.Name & " - Cell " & Target.Address
End Sub

Private Sub Workbook_Open()
    ' Log workbook open event (example)
    LogChange "Workbook Opened"
End Sub

' Custom logging procedure
Sub LogChange(ByVal logEntry As String)
    ' Insert your code to log changes, e.g., writing to a specific worksheet or file
    ' For simplicity, this example just prints to the Immediate Window
    Debug.Print logEntry
End Sub

 

This example logs events like sheet activation, deactivation, and cell changes. You can expand it to capture protection-related events based on your specific needs.

Remember to enable macros when using VBA code in Excel. Also, note that this example is very basic, and you may need to adapt it to your specific requirements. The text, steps and formulas was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.