Hello Excellers. Welcome back to another #MacroMonday #ExcelTip blog post in my Excel 2021 series. Do you need to password protect all of your Excel worksheets in a workbook at the same time?. Yes?. Well., you have come to the right Excel tip blog post. Let’s get coding. This really is a simple but very useful few lines of code.
Starting The Macro.
First, open the Visual Basic Editor. There are two ways to do this.
- Hit ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic.
Both methods have the same result. If you do not see the Developer Tab in your Excel worksheet then you need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.
Once you have your Developer Tab, go right ahead and Insert a New Module. You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. A simple explanation is if you want to run the macro only in the workbook you are using then insert your module in the current workbook. If you want to re-use the macro in all of your workbooks, then save it in your Personal Macro workbook.
Feel free to read more about your Personal Macro Workbook in the links at the bottom of this blog post.
After the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro ProtectAllWorksheets(). You will see that Excel automatically inserts the End Sub ending code once we create a new macro.
Sub ProtectAllWorksheets()
End Sub
Step 1. Declaring Variables.
First, any variables need to be declared. This creates a memory container in Excel for any values we want Excel to store. In this macro two variables need to be declared.
Dim ws As Worksheet
Dim pw As String
Step 2. Collect The Password From The User.
The password variable is set as the string collected from the user. An Input Box is used to collect this password information. I have instructed a very simple message box. An instruction to the user to input a password and then Ok button to enter the password.
Read more about the syntax of the VBA Input box in this blog post.
Macro Mondays – Excel VBA InputBox Function – The Basics.
This is a very efficient way to enable a user to supply information.
pw = InputBox("Enter a Password.", vbOKCancel)
Step 3. Use the For Each Loop.
So, onto the next part of the code. I am using the For Each loop to run through each of the worksheets in the active workbook and set the Password as the string supplied by the user in Step 2. This loops until all worksheets have been password protected.
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=pw
Next ws
Step 4. Ending The Macro.
Finally. the code ends when all of the worksheets have been password protected. This line of code already is in the module as part of the naming of the macro.
End Sub
The Full VBA Code.
Here is the full VBA code to use.
Disclaimer Note. Please always make a backup of any work before running any code.
Sub ProtectAllWorksheets()
'macro by www.howtoexcelatexcel.com
Dim ws As Worksheet
Dim pw As String
pw = InputBox("Enter a Password.", vbOKCancel)
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=pw
Next ws
End Sub
Learn More About Your Personal Macro Workbook (PMW)
Therefore, if you want to read more about your Excel PMW then check out my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?