A Macro to Protect Worksheets on Workbook Close

By Michael Alexander

Sometimes you need to send your Excel workbook out into the world with specific worksheets protected. If you find that you’re constantly protecting and unprotecting sheets before distributing your workbooks, the macro here can help you.

How the macro works

The code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The macro automatically protects the specified sheet with the given password, and then saves the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Protect the sheet with a password
     Sheets("Sheet1").Protect Password:="RED"
'Step 2: Save the workbook
     ActiveWorkbook.Save
End Sub

In Step 1, you’re explicitly specifying which sheet to protect — Sheet1, in this case. You also provide the password argument, Password:=”RED”, which defines the password needed to remove protection.

This password argument is optional. If you omit it, the sheet will still be ­protected, but you won’t need a password to unprotect it.

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

Step 2 tells Excel to save the workbook. If you don’t save the workbook, the sheet protection you just applied won’t be in effect the next time the workbook is opened.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro here allows it to run each time you try to close the workbook:

  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 BeforeClose 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 BeforeClose event.

    Enter your code in the Workbook BeforeClose event.

Note that you can protect additional sheets by adding addition statements before the Activeworkbook.Save statement.