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

Get FREE Advanced Excel Exercises with Solutions!

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:="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.

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, 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

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 “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 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


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 “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

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 “Exceldemy@0043” which is given in the below screenshot.

Unprotect Excel Sheet with Password Using VBA Code with Worksheet Name


Similar Readings


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


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 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

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo