How to Check if Worksheet is Protected or Unprotected

bisel

Board Regular
Joined
Jan 4, 2010
Messages
176
Office Version
  1. 365
Platform
  1. Windows
Hello All,

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

VBA Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,596
Office Version
  1. 365
Platform
  1. Windows
You could use
VBA Code:
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

Board Regular
Joined
Jan 4, 2010
Messages
176
Office Version
  1. 365
Platform
  1. Windows
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 ...

VBA Code:
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 ...

VBA 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,596
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for letting us know.
 

Forum statistics

Threads
1,147,518
Messages
5,741,637
Members
423,675
Latest member
Dea21

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