Do you have an Excel workbook with multiple sheets that you want to protect from being edited or deleted? You can create a macro to protect worksheets with vba in your workbook with just a few clicks. I will show you how to do it. Stay safe and protected!
I have several workbooks that I have to protect and unprotect regularly to update and then share with many users. Some of those Excel workbooks have a lot of individual worksheets. To illustrate, you can see an extract of an example Excel workbook below.
There are three stages of protection in Excel. These apply to the cell, the worksheet and the workbook. By default, all cells have a locked status. Select a cell or range of cells in any worksheet, 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.
Protecting Excel Worksheet.
- 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 all of my Excel worksheets. Let’s make this a lot easier with a simple bit of VBA 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.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Step 1. Insert A New Module. Name Your Excel Macro.
In the first place- Insert a new Module (I am inserting this module in my Personal Macro Workbook to store the macro). At this stage I name the Excel macro. I have named this Macro ProtectAllWorksheets. Notice Excel automatically inserts the End Sub line of code. All other lines of VBA code should be between these two lines.
Sub ProtectAllWorksheets()
End SubStep 2. Declaring Variables.
The next step is to declare any variables in the macro. This instructs Excel to allocate a memory container to store this information. The variable is this macro is below.
Dim wsheet As WorksheetStep 3. Using The For Each Loop.
Next, we use the For Each loop to instruct Excel to loop through all worksheets in the Excel workbook. The For Each method loops through all items in a collection. The collection in this example is the worksheets in our Excel workbook. We use the Active.Workbook so no other Excel workbooks are affected, only the current active one. Makes sense right?.
For Each wsheet In ActiveWorkbook.WorksheetsStep 4. Applying The Password.
As each worksheet is looped, the password is set to “password”. This is simply an example to work through. I would advise against using “password” as your password for obvious reasons.
wsheet.Protect Password:="password"
Next wsheetStep 5. Ending The Macro
Now, once all worksheets have the password applied, the routine ends. TheEnd Sub line of code is already present.
End SubIn 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. I hope you found it useful to learn how to protect worksheets with VBA.
Do You Want To Copy The VBA Code?
'macro by HowToexcelAtexcel.com
Sub ProtectAllWorksheets()
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Protect Password:="password"
Next wsheet
End SubIf 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, 50 Excel Tips.
