Macro 5: Unprotect a Worksheet on Workbook Open
If you’ve distributed workbooks with protected sheets, you likely get the workbooks back with the sheets
still protected. Often, you need to unprotect the worksheets in a workbook before continuing your work. If
you find that you are continuously unprotecting worksheets, this macro may be just the ticket.
How it works
This code is triggered by the workbook’s Open event. When you open a workbook, this event trig-
gers, running the code within. This macro automatically unprotects the specified sheet with the
given password when the workbook is opened.
Private Sub Workbook_Open()
‘Step 1: Protect the sheet with a password
Sheets(“Sheet1″).Unprotect Password:=”RED”
End Sub
The macro explicitly names the sheet we want to unprotect — Sheet1, in this case. Then it passes the
password required to unprotect the sheet. Be aware that Excel passwords are case-sensitive, so pay
attention to the exact password and capitalization that you are using.
How to use it
To implement this macro, you need to copy and paste it into the Workbook_Open event code
window. Placing the macro here allows it to run each time the workbook opens.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click ThisWorkbook.
4. Select the Open event in the Event drop-down list (see Figure 2-5).
5. Type or paste the code in the newly created module, modifying the sheet name (if necessary)
and the password. Note that you can unprotect additional sheets by adding additional
statements.
Figure 2-5: Type or paste your code in the Workbook_Open event code window.
