How to tell if someone unprotected a worksheet

anthonydib29

New Member
Joined
Oct 10, 2020
Messages
1
Office Version
  1. 2019
  2. 2016
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,173
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
Jun 24, 2010
Messages
815
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)
VBA Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,368
Messages
5,624,286
Members
416,018
Latest member
mirceaon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top