Learn

Macro 27: Unprotect All Worksheets

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 InsertModule.

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.

learn
We will be happy to hear your thoughts

Leave a reply

Share knowledge
Learn
Logo
Enable registration in settings - general