This blog post will show you how to create an Excel Macro using VBA to protect multiple Excel worksheets in an Excel workbook. Have you got multiple sheets you need to protect in an Excel workbook?. It can be quite time-consuming to individually protect them. I have a number of workbooks that I regularly have to protect and unprotect on a regular basis to update and then share them with a number of users. Some of those Excel workbooks have a lot of individuals worksheets. To illustrate, you can see an extract of an example excel workbook of mine below.
If we understand that the three stages of protection in Excel are those that apply to the cell, the worksheet and the workbook, and by default, all cells are locked. In any worksheet, select a cell or range of cells then hit CTRL+1 or right click and you will see that the Locked option is ticked. With this in mind, however, this setting is ineffective until you protect the worksheet. For this, you need to (in Excel 2016) another versions may vary slightly.
- Hit the Review Tab
- Protect Group
- Protect Sheet
- Select the options that you want or do not want the users to be able to carry out
- Type your password
Hence, I would have to repeat this over 50 times, to protect my Worksheets in my example workbook. Let’s make this a lot easier with a simple bit of VB code and I will show you in this blog post how to write an Excel Macro. Onto the Code!
In order to use this Macro in any workbook I am going to save this Macro in my Personal Macro Workbook, To learn more about the Personal Macro Workbook check out my blog posts below.
In the first place- Insert a new Module (I am inserting this module in my Personal Macro Workbook to store the macro).
Step 1. Then we need to declare a variable (this instructs Excel to allocate a memory container to store this information). The variable is
Dim wsheet As Worksheet
Step 2. Next, we are going to instruct Excel to protect multiple excel worksheets in our workbook then we need to loop through all of the worksheets using the For Each loop as this loops through each item in a collection. This is the collection of Worksheets in our Workbook.
Step 3. Consequently, the password specified is applied to each worksheet in the workbook.
Step 4. Then, each worksheet is looped through.
Step 5. Now, once all worksheets have the password applied, the routine ends.
Step 6. In conclusion, (my favourite part), Test Your code!
In short, I do find this code useful for sharing Excel documents that are read-only such as static reports that the user does not need to interact with using slicers or filters for example.
NEXT STEP, Do You Want To Copy The VBA Code?
If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.