Is My Worksheet Protected? Find Out With This Excel Macro

Hello Excellers and welcome to another #Excel tip in my #MacroMonday blog post in my series of articles. Do you know how to tell if your Excel worksheet is protected? With this handy macro, you can find out in just a few seconds if you have protection on your Excel worksheet. If you did not create the Excel workbook then you may not know if the author did protect the Excel worksheet.

A Macro And A User Defined Function.

So, today let’s write an Excel Macro that will identify if any worksheets in the active workbook have protection. We take this Macro one step further by creating our very own Excel function or a User Defined Function (UDF). This new function is called when needed, to confirm if worksheets have protection.

If you want to read my previous blog post on Creating A User Defined Function or Executing A Function Procedure In Excel then click on the links below.

Executing A Function Procedure In Excel

Create User Defined Function (UDF) And Excel Add-In To Count Cells By Their Colour – Macro Monday

In the way that every Sub procedure begins with the words Sub, Functions always begin the word Function. Functions end with the words End Function. Most calculations can be achieved with what is provided by default within Excel. After you have used the Excel application for a length of time it isn’t long before you find yourself wishing that there was a function that did a particular job. If you cannot anything suitable on the list of in built function then you need a UDF. (User Defined Function). Now, these can be easy to the most complex, but the theory is the same, you want Excel to do a calculation to return a single value back.

Preparing To Write The Macro.

Step 1. Open Visual Basic And Name Your Function,

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

Start the code by typing Function then type the name of your UDF. I have chosen to name it ProtectedWorksheets. Just as when you type Sub for a procedure, yes that is right we type Function to begin writing our function. Notice that Excel automatically put the End Function at the end of the code for us.

Function ProtectedWorksheets() As Boolean
End Function

Step 2. Declare Any Variables.

We need to declare one variable in this function. This action simply creates a memory container in Excel for this value. This is the name of the worksheet.

Dim sht As Worksheet

Step 3. Use The For Each Loop.

We now use the For Each Loop to loop through all of the worksheets in the Active workbook to see if they have protection. A loop allows us to conduct the same kind of activity for many cells or objects in this case in Excel. The syntax is simple.

For Each Object In Collection | Do something | Next Object

Excel tests each worksheet for protection. If protection is enabled on the worksheet then the result of the formula is TRUE. If there is no protection on sheets the result of the function is false.

For Each sht In ActiveWorkbook.Sheets
If sht.ProtectContents = True Then
ProtectedWorksheets = True
Exit Function
End If
Next sht
WorksheetProtected = False

Step 4. Ending The Macro.

The routine then ends with the End Sub piece of code. This was already entered into the module for us when started the type the name of the Macro.

End Function

Final Step Running The Function.

So, this is the final step. Testing the Excel function. We know that we can call this function just like any other, so let’s just do that. Type =ProtectedWorksheets() in a cell you want the result of TRUE (worksheet protection is on) or False (no worksheets are protected).

If you’re curious about whether your worksheet is protected or not, this Excel macro can help. The code will run through each sheet in the workbook and tell you if it is protected.