Excel

Check if Worksheet is protected

Ease of Use

Easy

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

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

Discussion:

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. 

Code:

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 265 times.

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