Macro 26: Protect All Worksheets
Before you distribute your workbook, you may want to apply sheet protection to all of the sheets.
Instead of protecting each sheet manually, you can use this macro.
How it works
In this macro, you are looping the worksheets and simply applying protection with a password. The
Password argument defines the password needed to remove the protection. The Password argu-
ment is completely optional. If you omit it altogether, the sheet will still be protected; you just won’t
need to enter a password to unprotect it. Also, be aware that Excel passwords are case-sensitive, so
you’ll want to pay attention to the exact capitalization you are using.
Sub Macro26()
‘Step 1: Declare your variables
Dim ws As Worksheet
‘Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
‘Step 3: Protect and loop to next worksheet
ws.Protect Password:=”RED”
Next ws
End Sub
1. Step 1 declares an object called ws. This creates a memory container for each worksheet we
loop through.
2. Step 2 starts the looping, telling Excel we want to enumerate through all worksheets in this
workbook.
3. In Step 3, the macro applies protection with the given password, and then loops back to get
the worksheet.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it is named personal.xlsb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a macro
using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list. Simply record a couple of cell clicks and then stop recording. You can discard the recorded
macro and replace it with this one.
