Protect a Worksheet on Workbook Close Excel VBA

Learn how to protect a worksheet on workbook close

This is very important when you find that you’re constantly protecting and unprotecting worksheets before distributing your workbooks

How it works (Protect a Worksheet on Workbook Close)

This 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 worksheet with the given password, and then saves the workbook.

Ready to use/apply VBA Code (double click to select entire code)


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Protect the sheet with a password of your choice
Sheets(“Sheet1”).Protect Password:=”RED”

'Step 2: Save the workbook, you can also use ThisWorkbook instead of Activeworkbook here
ActiveWorkbook.Save

End Sub

In Step 1, we are explicitly specifying which sheet we want to protect — Sheet1, in this case.
We are also providing the password argument, Password:=RED. This defines the password needed to remove the protection.
This password argument is completely optional. If you omit this altogether, the sheet will still be protected, but you won’t need a password to unprotect it.
Also, be aware that Excel passwords are case-sensitive, so you’ll want pay attention to the exact password and capitalization that you are using.
Step 2 tells Excel to save the workbook.
If we don’t save the workbook, the sheet protection we just applied won’t be in effect the next time the workbook is opened.

How to use it? and Protect a Worksheet on Workbook Close

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.

Activate the Visual Basic Editor by pressing ALT+F11.
In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.
Click ThisWorkbook.
Select the BeforeClose event in the Event drop-down list (see GIF animation below).
Type or paste the code in the newly created module, modifying the sheet name (if necessary) and the password.
Note that you can protect additional sheets by adding additional statements before the ActiveWorkbook.Save statement.

Also check out my blog post on how to Create New Workbook using Excel VBA automatically here

If you want to master Excel in Professional way, check out my FREE EBook available for download, this Ebook contains more of these ready to apply Excel VBA codes and formulas! (Just fill below form to get this amazing Ebook for FREE)

If you have any question or query regarding above code usage, let me know in the comments below!