Macro 27: Unprotect All Worksheets
You may find yourself constantly having to unprotect multiple worksheets manually. The following
macro does the same thing programmatically.
How it works
This macro loops the worksheets and uses the Password argument to unprotect each sheet.
Sub Macro27()
‘Step 1: Declare your variables
Dim ws As Worksheet
‘Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
‘Step 3: Loop to next worksheet
ws.UnProtect 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 to enumerate through all worksheets in this workbook.
3. Step 3 unprotects the active sheet, providing the password as needed, and then loops back
to get the worksheet.
Obviously, the assumption is that all the worksheets that need to be unprotected have the same
password. If this is not the case, you need to explicitly unprotect each sheet with its corresponding
password.
Sub Macro27b()
Sheets(“Sheet1″).UnProtect Password:=”RED”
Sheets(“Sheet2″).UnProtect Password:=”BLUE”
Sheets(“Sheet3″).UnProtect Password:=”YELLOW”
Sheets(“Sheet4″).UnProtect Password:=”GREEN”
End Sub
95 Macro 27: Unprotect All Worksheets
How to use it
The best place to store this kind of a 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 will be 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 means 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.
