While working with Microsoft Excel, sometimes we need to unprotect our protected dataset for the convenience of our work. Basically, we may need to unprotect the Excel sheets with passwords for several reasons. One is to unlock cells to protect them from further edits or changes whereas the other reason is to use the unlocked cells as absolute cell references. Today, in this article, we’ll learn three quick and suitable ways to unprotect Excel sheets with passwords using VBA code in Excel effectively with appropriate illustrations.
Excel VBA to Unprotect Sheet with Password (Quick View)
Sub Unprotect_Excel_Sheet()
Sheets("VBA").Unprotect Password:="Exceldemy@0043"
End Sub
Unprotect Excel Sheet with Password Using VBA: 3 Suitable Ways
Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The delivery and ordered product and the revenue earned by the sales representatives are given in columns D, C, and E respectively. We will unprotect our dataset by applying the VBA macros. Using VBA code to unprotect Excel sheets with passwords is an easy task. Here’s an overview of the dataset for today’s task.
1. Perform a VBA Code to Unprotect Single Excel Sheet with Password in Excel
Now I’ll show how to unprotect 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 unprotect an Excel sheet with a password. Let’s follow the instructions below to unprotect 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 – Unprotect Excel sheet 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, a new module will be opened. In the module, write down the below VBA code.
Sub Unprotect_Excel_Sheet()
Sheets("VBA").Unprotect 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 unprotect the Excel sheet named VBA with the password “Exceldemy@0043” which is given in the below screenshot.
- Now, we will check whether our unprotected Excel sheet will work or not. To do that, select cell B15, and if you are able to write down anything in the selected cell, the unprotected Excel sheet will work which has been given in the below screenshot.
Read More: Excel VBA: Unprotect Workbook with Password
2. Unprotect Excel Sheet with Password Using VBA Code with Worksheet Name
In this method, we will unprotect the Excel sheet with a password using the VBA code. From our dataset, we will unprotect the Excel sheet named Unprotect with the password “Exceldemy@0043“. Let’s follow the instructions below to unprotect the Excel sheet 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 Unprotect_Excel_Sheet_with_Password()
Dim R As Worksheet
Set R = Worksheets("Unprotect")
R.Unprotect 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 Unprotect with the password “Exceldemy@0043” which is given in the below screenshot.
3. Unprotect All Excel Sheets with Password Using VBA Code
Last but not the least, we will unprotect 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 unprotect all Excel sheets with 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 Unprotect_All_Excel_Sheets()
Dim x As Worksheet
For Each x In Worksheets
x.Unprotect "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 unprotect all Excel sheets.
- Now, we will check whether our unprotected Excel sheet will work or not. To do that, select cell B15 in any of the Excel sheets, and if you are able to write down anything in the selected cell, the unprotected Excel sheet will work which has been given in the below screenshot.
Read More: Excel VBA: How to Unprotect Excel Sheet Without Password
Things to Remember
👉 You can pop up the 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 unprotect Excel sheets 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. Goodbye!
Related Articles
- Excel VBA: Unprotect Workbook without Password
- Excel VBA: Protect Workbook with Password
- Excel VBA: Protect Sheet with Password and Allow Filter
- Excel VBA to Protect Sheet but Allow to Select Locked Cells
