VBA Express : Excel - Check if any sheets in workbook are protected

Excel

Check if any sheets in workbook are protected

Ease of Use

Intermediate

Version tested with

97, 2000, 2003

Submitted by:

Ken Puls

Description:

This function allows you to test if any of your sheets are protected before you start executing code on them.

Discussion:

It is always a good idea to test your environment in advance as much as possible to make sure that your code will run without errors. If at all possible, you never want to run into a situation where your code completes part of it's task and then runs into an error, as cleaning up in the middle is never fun. This code allows you to test if any sheets are protected before you run your code so that sheet protection doesn't foil you. It simply returns the value TRUE if one or more sheets are protected, and FALSE if none are protected.

Code:

instructions for use

			Option Explicit 
 
Public Function wbSheetsProtected(wbTarget As Workbook) As Boolean 
     'Macro Purpose:  To test if any worksheet in the workbook is protected
     '   Returns False if no sheets protected
     '   Returns True if one or more sheets protected
     
    Dim ws As Worksheet 
     
     'Set function to False
    wbSheetsProtected = False 
     
     'Loop through each worksheet and check for protection
    For Each ws In wbTarget.Worksheets 
        If ws.ProtectContents = True Then 
             'If protected, set function to True and exit
            wbSheetsProtected = True 
            Exit Function 
        End If 
    Next ws 
     
End Function

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
You will also need to create your own procedure to call the Function (see example file if you are not clear how to do this.)
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

Make sure that all your sheets are unprotected.
In the Immediate window in the VBE, type "? wbSheetsProtected(ActiveWorkbook)" (no quotes) and hit Enter
You should see FALSE returned.
Protect at least one sheet
In the Immediate window in the VBE, type "? wbSheetsProtected(ActiveWorkbook)" (no quotes) and hit Enter
You should see TRUE returned.

Sample File:

FuncShtProtectd.zip 8.26KB

Approved by mdmackillop

This entry has been viewed 151 times.