A Macro to Unprotect a Worksheet on Workbook Open

By Michael Alexander

If you’ve distributed Excel 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 the macro works

The code is triggered by the workbook’s Open event. When you open a workbook, this event triggers, 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 you want to unprotect — Sheet1, in this case. Then it passes the password required to unprotect the sheet.

Excel passwords are case-sensitive, so pay attention to the exact password and capitalization that you are using.

How to use the macro

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 is opened:

  1. Activate 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 to see all the sheets.

  3. Click ThisWorkbook.

  4. In the Event drop-down list, select the Open event.

  5. Type or paste the code in the newly created module, modifying the sheet name (if necessary) and the password.

    Enter your code in the Workbook Open event.

    Enter your code in the Workbook Open event.