Hello Excellers, it is time for some more cool code in the #macromondays series. It can be a pain to remember to protect your worksheets every time you close your Excel workbook. But with a few simple steps, you can automatically protect them for added security. In this post, we’ll show you how to protect a workbook in Excel automatically. the process requires a simple but effective Excel VBA macro. So read on and learn how to make your data extra safe!
So do you like qtching Excel tips and working along with a solution. If the answer is yes, then I invite you to subscibe to my YouTube channel it is free with Excel tips every week that a quick and simple to follow. Quick tips for busy people. Click the link below to take you direct to my channel. So, looking forward to seeing you over there!.
So, back to #macromonday and today let’s look at automatically triggering a worksheet to be password protected as you close the workbook. Yes, this article is 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 every 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. (i know it so cool).
How Does It Work?
The code is triggered by the BeforeClose event, which occurs before the workbook closes. If the workbook has been changed, then this event occurs before the user is asked to save changes by Excel. So, when decide to close the Excel workbook the VBA code is activated. Excel automatically protects the specified worksheet with the password you supply in the VBA Code. Finally, Excel saves the workbook.
Let’s go right ahead and write the macro to proect your Excel workbook automatically.
Step 1. Activate The Visual Basic Editor.
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 2. Using The BeforeClose Event.
This is the core of the VBA code. Go ahead and select the BeforeClose event in the drop down list. It is by doing this we specifically tell Excel which sheet we want to protect ( in this example it is “Sheet1”). To make the code more applicable to your Excel project then this is where you change the
Step 3. Supply The Password.
The next step is to supply the password to use to protect the worksheet. (If you do not specify a password you can trigger the protection of the sheet. Consequenty users can easily just select the un-protect option to gain access to edit the worksheet).
Step 4. Close The Workbook And End The Macro.
The final step in this process is to save the Excel workbook and close it . This is where the macro ends. Al that is left is to test your new macro. Why not copy the code below and give this one a go.
Copy The Code To Protect A Workbook In Excel Automatically.
Feel free to use the cde and alter it for your own use in your own Excel workbook solution. Note- always back up your excel files before running any code.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(Sheet1).Protect
Password = “mypassword”
ActiveWorkbook.Close
End Sub
Now that you know how to write a Excel vba Macro to protect a workbook in Excel automatically, be sure to sign up for my newsletter below so you can receive more tips like this one straight to your inbox. In the meantime, if you have any questions or feedback about this tutorial, please don’t hesitate to leave me a comment below and I will get back to you as soon as possible. Thanks for reading!
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.
How To Excel At Excel – Macro Mondays Blog Posts.
