VBA Macro to Protect all Worksheets in an Excel Workbook

This tutorial is going to take a look at how to protect all worksheets in a workbook. So rather than having to protect each worksheet individually using the Protect Worksheet command we are going to create a macro that protects all the sheets in one go. We will also create a macro that unprotects the sheets. This will work for any workbook you open in Excel.

To start with you need to display the Developers tab on the Ribbon. Right-click on any Ribbon tab and select Customize the Ribbon. Now make sure the Develop option is ticked in the Excel Options dialog – see below. Click OK to confirm.

On the Developer tab click the Visual Basic button to open the Visual Basic Editor or use the shortcut ALT F11.

The Personal Macro Workbook

We are going to save our Macros in the Personal Macro Workbook – any macros saved here are made available to all workbooks. You will see this workbook listed as PERSONAL.XLSB in the Project Window. If this workbook isn’t listed here it has not been created – you will need to save a recorded macro to the Personal Macro Workbook within Excel first – just something simple.

You can either store your macro in an existing module within the Personal Macro Workbook or create a new one. To create a new Module right-click on the Personal Macro Workbook and select Insert > Module

Select the Module you want to use and then in the Code window start to type your macro.

Writing the Macro

Start by naming your macro

Sub ProtectSheets()

Then declare your variable. A variable is a container that stores information. We have named our variable wsheet. To declare a variable you enter Dim (Dimension), the name of the variable and the type of variable.

Dim wsheet As Worksheet

We are going to ask Excel to apply the protection to each sheet in a workbook. We can use a type of looping for this that loops through each object in a collection: our Workbook has a collection of worksheets. Here is the code that performs the loop: notice how we use our variable wsheet in the code.

For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Protect Password:=”password”
Next wsheet

The macro ends with End Sub – this line of code is probably already present.

The final code:

Sub ProtectSheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Protect Password:="password"
Next wsheet
End Sub

Creating the Macro to Unprotect Sheets

The macro for unprotecting sheets is very similar – I’ve highlighted the changes in bold.

Sub unprotectsheets() 
Dim wsheet As Worksheet 
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Unprotect Password:="password" 
Next wsheet 
End Sub

Assigning the Macro to Quick Access Tool Buttons

The next step is to assign the macros to buttons on Excel’s Quick Access Toolbar. Close down the Visual Basic Editor – don’t worry about saving at this stage. In Excel click on the customize button at the end of the Quick Access Toolbar and select More Commands.

The Excel Options dialog opens. In the Choose commands from menu select Macros.

Your macros should be listed here. You can now add them to your Quick Access Toolbar. Use the modify button if you need to change the appearance of your buttons. Click OK to confirm and you are done.

When you close Excel you will be asked if you want to save changes to the Personal Macro Workbook – make sure you click Save.

VBA Macro to Protect all Worksheets in an Excel Workbook