This article illustrates how to unprotect a workbook that is protected with a password using VBA in Excel with 7 suitable examples. Excel allows the feature to protect a workbook with or without passwords to protect the worksheets from editing, deleting, and even copying accidentally or deliberately. In case, we have a bunch of protected workbooks with the same or different passwords, it’s a time saver to unprotect all of them by running a simple VBA code. Let’s dive into the examples to learn more.
Excel VBA Unprotect Workbook with Password: 7 Suitable Examples
To show different approaches to unprotecting a workbook with a password, we’ve created 4 Excel workbook files inside a folder.
All these workbooks are initially protected with the same password. A protected workbook prevents a user from doing any operation over the worksheets. The user cannot edit, move, delete or hide any worksheet in the protected workbook. Moreover, by adding a password one can secure the structure of the workbook.
The above screenshot shows the disabled options to insert, delete, rename, move or copy, hide, and unhide options in a protected workbook.
In the following examples, we’ll be using the Workbook. Unprotect method to configure our VBA code. The method removes protection from a protected workbook with or without a password. The only argument is the password. The argument is required for a workbook protected with a password.
Write Code in Visual Basic Editor
To unprotect a workbook in Excel, we need to open and write VBA code in the Visual Basic Editor. Follow the steps to open the Visual Basic Editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.
Now put your code inside the visual code editor and press F5 to run it.
1. Unprotect a Single Workbook with a Password in Excel VBA
Task: Unprotect the workbook named workbook_1.xlsx inside the Exceldemy folder using VBA code. The password used to protect the workbook is 123456.
Code: Insert the following code inside the visual basic editor of workbook_1.xlsx and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
Workbooks("workbook_1.xlsx").Unprotect Password:=123456
End Sub
Output: Right–clicking on the sheet name shows the enabled options i.e., insert, delete, rename, move or copy, hide, and more. Moreover, the Protect Sheet button is not highlighted anymore.
Read More: Excel VBA: Unprotect Workbook with Password
2. Excel VBA Code to Unprotect Active Workbook with Password
Task: We want to configure our code to unprotect only the active workbook protected with a password. In many cases, we need to open multiple workbooks at a time. But we want to unprotect only the active workbook we’re working on.
Code: Insert the following code inside the visual basic editor of workbook_1.xlsx and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
ActiveWorkbook.Unprotect Password:=123456
End Sub
In the above screenshot, we can see that we’re running the code inside the visual basic editor for the workbook_1.xlsx workbook. On the other hand, the workbook_2.xlsx is the active workbook. The code will unprotect the active workbook instantly.
3. Unprotect the Current Workbook with a Password in Excel VBA
Task: We want to unprotect only the current workbook (workbook_1.xlsx in this example) with a password.
Code: Insert the following code inside the visual basic editor of workbook_1.xlsx and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
ThisWorkbook.Unprotect Password:=123456
End Sub
The above screenshot shows that we’re running the code inside the visual basic editor of the workbook_1.xlsx workbook. It’ll unprotect the current workbook.
4. Apply VBA Code to Unprotect All Open Workbooks with the Same Password
Task: There are multiple protected workbooks with the same password opened at the same time. We want to configure our code that’ll unprotect them all.
Solution: We’ll use a For…Each loop to apply the Workbook.Unprotect method to all the open workbooks.
Code: Insert the following code inside the visual basic editor and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
 Dim wbook As Workbook
   For Each wbook In Workbooks
       wbook.Unprotect Password:=123456
   Next wbook
End Sub
5. Unprotect All Open Workbooks with Different Passwords
Task: There are multiple protected workbooks with different passwords opened at the same time. We want to configure our code that’ll unprotect them all.
Problem Analysis: Among the 4 workbooks in the Exceldemy folder, workbook_3.xlsx and workbook_4.xlsx have different passwords than the other workbooks. So, we need to set the passwords individually for each of the workbooks.
Code: Insert the following code inside the visual basic editor and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
Workbooks("workbook_1.xlsx").Unprotect Password:=123456
Workbooks("workbook_2.xlsx").Unprotect Password:=123456
Workbooks("workbook_3.xlsx").Unprotect Password:=111111
Workbooks("workbook_4.xlsx").Unprotect Password:="password"
End Sub
6. Run a VBA Code to Unprotect All Workbooks Except Specific Ones
Task: Unprotect all the opened workbooks that are protected with the same password except the workbook_2.xlsx.
Solution: We’ll apply an If…Then…Else statement to check the workbook name. If the conditional finds the workbook then it’ll leave it protected and unprotect the others.
Code: Insert the following code inside the visual basic editor and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
 Dim wbook As Workbook
   For Each wbook In Workbooks
   If wbook.Name = "workbook_2.xlsx" Then
       wbook.Protect (123456)
   Else
       wbook.Unprotect (123456)
   End If
   Next wbook
End Sub
7. Unprotect a Closed Workbook with Password in Excel VBA
Task: We need to unprotect a closed workbook that is protected with a password.
Solution: In our code, we need to specify the workbook location. Then we’ll use the Workbook. Open the method to open the workbook before applying the Workbook. Unprotect method with a password. To get the exact file location, press Shift and right-click on the workbook to copy the path.
Code: Insert the following code inside the visual basic editor and press F5 to run it.
Sub UnprotectWorkbookWithPassword()
Dim wbook As Workbook
Set wbook = Workbooks.Open("D:\Exceldemy\workbook_4.xlsx")
Application.ScreenUpdating = False
wbook.Unprotect (123456)
wbook.Close SaveChanges = True
Application.ScreenUpdating = True
End Sub
Things to Remember
- The password argument of the Unprotect method is case-sensitive.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Now, we know how to unprotect all workbooks with passwords in Excel VBA with 7 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
Related Articles
- How to Unprotect Excel Sheet with Password Using VBA
- Excel VBA: Unprotect All Sheets
- 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
- Excel VBA: How to Unprotect Excel Sheet Without Password
