While working with Microsoft Excel, sometimes we need to protect our dataset for the convenience of our work. Basically, we may need to protect selected cells for two reasons. One is to lock cells to protect them from further edits or changes whereas the other reason is to use the locked cells as absolute cell references. Today, in this article, we’ll learn 3 quick and suitable ways of using Excel VBA to protect workbooks with passwords.
Excel VBA to Protect Workbook with Password (Quick View)
Sub Protect_All_Excel_Sheets()
Dim x As Worksheet
For Each x In Worksheets
x.Protect "Exceldemy"
Next x
End Sub
Let’s say, we have a dataset that contains information about several Sales Rep of the Armani group. The Delivered and Ordered products and the Rev Earned by the sales representatives are given in columns D, C, and E respectively. We will protect our dataset by applying the VBA macros. Using VBA code to protect Excel workbooks with passwords is an easy task. Here’s an overview of the dataset for today’s task.
1. Using VBA Code to Protect an Excel Workbook with Password
Now I’ll show how to protect an Excel sheet with a password by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will protect an Excel sheet with a password. Let’s follow the instructions below to protect an Excel sheet with a password!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Protect Workbook will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, the Protect Workbook module pops up. In the Protect Workbook module, write down the below VBA
Sub Protect_Excel_Sheet()
Sheets("VBA").Protect Password:="Exceldemy@0043"
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the VBA Code, you will be able to protect the Excel sheet named VBA with the password “Exceldemy@0043” which is given in the below screenshot.
Step 3:
- Now, we will check whether our protected Excel sheet will work or not. To do that, select cell B7.
- Hence, write down anything in the selected cell, if you get a warning message named Microsoft Excel that appears in front of you then you will be able to protect the Excel sheet which has been given in the below screenshot.
Read More: Excel VBA: Protect Sheet with Password and Allow Filter
2. Protecting Excel Workbook with Password Using VBA Code with Worksheet Name
In this method, we will protect the Excel workbook with a password using a VBA code. From our dataset, we will protect the Excel workbook named Protect with the password “Exceldemy@0043“. Let’s follow the instructions below to unprotect the Excel workbook with a password!
Step 1:
- First, according to method 1, insert a new module and type the below VBA code to unprotect the Excel sheet with a password. The VBA code is,
Sub Protect_Excel_Workbook_with_Password()
Dim R As Worksheet
Set R = Worksheets("Protect")
R.Protect Password:="Exceldemy@0043"
End Sub
- Further, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the VBA Code, you will be able to unprotect the Excel sheet named Protect with the password “Exceldemy@0043” which is given in the below screenshot.
Read More: How to Unprotect Excel Sheet with Password Using VBA
3. Protecting All Excel Sheets with Password Using VBA Code
Last but not least, we will protect all Excel sheets with passwords by using a simple VBA code. This is an easy task and time-saving also. Let’s follow the instructions below to protect all Excel sheets with a password!
Step 1:
- First, insert a new module according to method 1 and type the below VBA code to change the font color. The VBA code is,
Sub Protect_All_Excel_Sheets()
Dim x As Worksheet
For Each x In Worksheets
x.Protect "Exceldemy"
Next x
End Sub
- Further, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After running the VBA Code, you will be able to protect all Excel sheets.
- Now, we will check whether our protected Excel sheet will work or not. To do that, select cell C10 in any of the Excel sheets.
- Hence, write down anything in the selected cell, if you get a warning message named Microsoft Excel that appears in front of you then you will be able to protect the Excel sheet which has been given in the below screenshot.
Read More: Excel VBA to Protect Sheet but Allow to Select Locked Cells
Things to Remember
👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to protect the Excel workbook with passwords with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.