How to Check if Worksheet is Protected or Unprotected

[bisel]

Hello All,

I found this small function that checks if a worksheet is protected or not ...

Function IsSheetProtected(s As String) As Boolean
' The value s is passed as the worksheet name
    Application.Volatile
    IsSheetProtected = Sheets(s).ProtectContents
End Function

... and it works OK, but there is one issue.

If say in cell B6 I enter this statement ... =IsSheetProtected("Financial Data")
then the value of B6 is either TRUE (sheet is protected) or FALSE (sheet is not protected).
However, if I right click on the sheet tab to change the protection of the sheet, the value cell B6 does not update unless I click on another cell in the sheet.

My objective is to have a cell on each sheet in the workbook to indicate the protection status of that sheet. Ideally, I would like a way to do this without referencing a
specific sheet name. For example, a function that would take the current sheet name or sheet number and test the protection status. Then update the cell with an
indicator of the protection status. And if the protection status is changed, to update the cell without having to activate another sheet and then come back or select a
different cell in the worksheet.

Thanks for any help,

Steve

 

[Fluff]

You could use

Function IsSheetProtected() As Boolean
    
    Application.Volatile
    IsSheetProtected = Sheets(Application.Caller.Parent.Name).ProtectContents
End Function

But as far as I know, there is no-way to track if the protection is changed.

 

[bisel]

I have derived a solution which I believe meets my needs. Invite others for any comments / review.

For each sheet in the workbook, I have this sub which runs when the sheet is activated ...

Private Sub Worksheet_Activate()

Dim reprotect As Boolean

' This routine to unprotect the sheet only necessary if the other stuff you want to do is easier if the sheet is unprotected.  The idea is to 
'   reprotect the sheet when other stuff is complete.

    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect
        reprotect = True
    Else
        reprotect = False
    End If

'....
'other stuff here.  If no other stuff is being done, then do not need to unprotect routine above.
'....

If reprotect = True Then
    ActiveSheet.Protect
    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.ProtectionButton.Caption = "Sheet Protected Click to Unprotect"
    ActiveSheet.ProtectionButton.BackColor = RGB(0, 155, 0)
  Else
    ActiveSheet.ProtectionButton.Caption = "Sheet UnProtected Click to Protect"
    ActiveSheet.ProtectionButton.BackColor = RGB(255, 0, 0)  
End If

Calculate

End Sub

And, I created a command button that I named, "ProtectionButton". This command button has the following code ...

Private Sub ProtectionButton_Click()
    If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect
    Else
        ActiveSheet.Protect
        ActiveSheet.EnableSelection = xlUnlockedCells
    End If
  
    Call Worksheet_Activate
      
End Sub

This seems to work and satisfies my needs. If anyone has any comments, I would love to hear them.

Thanks,

Steve

 

[Fluff]

Glad you sorted it & thanks for letting us know.