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.

Download Practice Workbook

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

7 Suitable Examples to Unprotect Workbook with Password Using VBA in Excel

To show different approaches to unprotect 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 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.

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 Current Workbook with 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, the 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 find 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 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.

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.

The post Excel VBA: Unprotect Workbook with Password (7 Suitable Examples) appeared first on ExcelDemy.