Hello Excellers, it is time for some more cool code in the #macromondays series. Today let’s look at automatically triggering a worksheet to be password protected as you close the workbook. This is really really useful if you are updating and distributing an Excel workbook, but find your self un-protecting, updating and the protecting an worksheets over and over again. ( I know I do. Every week and month). With this Macro, you can automate this process as you close the workbook, the specified password is applied. How convenient!.
What Does The Macro Do?
This Macro automatically password protects a worksheet as the Excel workbook closes.
How Does It Work?
The code is triggered by the BeforeClose event, so when you go to close the Excel workbook the code is activated and automatically protects the specified worksheet with your given password, then saves the workbook.
Let’s create the Macro!
First – Activate the Visual Basic Editor by pressing ALT+F11, in the Project Window find your project or workbook name and click the plus sign next to it to see all of the worksheets in it, then select ThisWorkbook.
Step 1. Select the BeforeClose even in the drop down list. We specifically tell Excel which sheet we want to protect ( in this example it is “Sheet1”)
Step 2. We now supply the password to use to protect the worksheet. (If you do not specify a password you can trigger the protection of the sheet, but users can easily just select the un-protect option to gain access to edit the worksheet).
Step 3. The last stage is to save the workbook and close it.
Step 4. Test your Macro!!
Want To Copy The Code?
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.
You can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.