Check if Worksheet is protected

Ease of Use


Version tested with

97, 2003 

Submitted by:

Ken Puls


This function checks to see if a worksheet is protected. Returns True if so or False if not 


This routine can be used to test if a worksheet is protected before attempting to do something to/with it through VBA code. It can be called through VBA, but unfortunately cannot be entered directly in a worksheet cell. Only the SheetProtected function (below) is required to test the protection setting, the "SimpleTest" subroutine is for illustration purposes only. 


instructions for use


Option Explicit Private Function SheetProtected(TargetSheet As Worksheet) As Boolean 'Function purpose: To evaluate if a worksheet is protected If TargetSheet.ProtectContents = True Then SheetProtected = True Else SheetProtected = False End If End Function Sub SimpleTest() 'Macro purpose: To demonstrate use of SheetProtected Function '*** THIS MACRO FOR ILLUSTRATIVE PURPOSES ONLY *** '*** AND IS NOT REQUIRED TO USE THE ABOVE FUNTION! *** 'Assign active worksheet to variable to be tested Dim ws As Worksheet Set ws = ActiveSheet 'Test the activesheet's protection If SheetProtected(ws) Then 'If protected MsgBox "Sorry, but " & ws.Name & " is protected!", _ vbOKOnly + vbInformation, ws.Name & " is protected!" Else 'If not protected MsgBox "Hooray! " & ws.Name & " is not protected!", _ vbOKOnly + vbInformation, ws.Name & " is unprotected!" End If End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. In Excel press Alt + F8 to call the macro
  2. Choose SimpleTest from the dropdown list
  3. A messagebox will pop up to display the current protection setting
  4. Clear the messagebox
  5. From the Tools Menu, choose Protection/(un)protect sheet (assign/enter a password if necessary)
  6. Follow steps 1 & 2 above to check sheet protection again

Sample File:

SheetProtectionFunction.zip 10.94KB 

Approved by mdmackillop

This entry has been viewed 292 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express