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

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