How to Unprotect Excel Sheet with Password Using VBA (3 Quick Tricks)

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 for protecting 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 sheet with password 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:="[email protected]"
End Sub


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Suitable Ways to Unprotect Excel Sheet with Password Using VBA

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.

Perform a VBA Code to Unprotect Single Excel Sheet with Password in Excel


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

Perform a VBA Code to Unprotect Single Excel Sheet with Password in Excel

Step 2:

  • Hence, the Unprotect Excel sheet module pops up. In the Unprotect Excel sheet module, write down the below VBA
Sub Unprotect_Excel_Sheet()
Sheets("VBA").Unprotect Password:="[email protected]"
End Sub

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Perform a VBA Code to Unprotect Single Excel Sheet with Password in Excel

  • After running the VBA Code, you will be able to unprotect the Excel sheet named VBA with the password [email protected] which has been 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 will able to write down anything in the selected cell, the unprotected Excel sheet will work which has been given in the below screenshot.

Perform a VBA Code to Unprotect Single Excel Sheet with Password in Excel

Read More: How to Unprotect Excel Sheet with Password (2 Quick Methods)


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 VBA code. From our dataset, we will unprotect the Excel sheet named Unprotect with the password “[email protected]“. 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:="[email protected]"
End Sub

Unprotect Excel Sheet with Password Using VBA Code with Worksheet Name

  • 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 [email protected] which has been given in the below screenshot.

Unprotect Excel Sheet with Password Using VBA Code with Worksheet Name

Read More: Excel VBA: How to Unprotect Excel Sheet without Password


3. Unprotect All Excel Sheets with Password Using VBA Code

Last but not the least, we will unprotect all Excel sheets with password 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

unprotect excel sheet with password vba

  • Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Unprotect All Excel Sheets Using VBA Code

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 will able to write down anything in the selected cell, the unprotected Excel sheet will work which has been given in the below screenshot.

Unprotect All Excel Sheets Using VBA Code

Read More: Excel VBA: Unprotect All Sheets (4 Suitable Examples)


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


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo