VBA to Unprotect a Workbook in Excel

You can use the ‘Workbook.Unprotect’ method to unprotect a workbook using VBA.

Let me first quickly explain the Workbook.Unprotect method, and then I’ll show you some examples of how you can use it to unprotect a workbook through VBA.

Table of Contents

The Workbook.Unprotect Method

The ‘Workbook.Unprotect’ method removes protection from a workbook to allow modifications to its structure. The method has no effect if the workbook isn’t protected.

The method’s syntax:

expression.Unprotect (Password)

expression is a variable representing a ‘Workbook’ object.

The method’s ‘Password’ argument is optional and of the Variant data type. This argument is ignored if the target workbook is not protected with a password.

The method fails if you leave out this argument or supply an incorrect password for a workbook that is protected with a password.

Also read: Protect Excel Workbook Using VBA

VBA to Unprotect the Current Workbook

If the current workbook is protected without a password, you can unprotect it and save it using the following subroutine:

Sub UnprotectCurrentWorkbook()
ThisWorkbook.Unprotect
ThisWorkbook.Save
End Sub

If the current workbook is protected with a password, you can use the code below to unprotect it and then save it:

Sub UnprotectPasswordCurrentWorkbook()
ThisWorkbook.Unprotect Password:="password"
ThisWorkbook.Save
End Sub

Note: Replace the password in the example code with your own. If you omit the ‘Password’ argument or supply an incorrect password, you will get the runtime error 1004:

Also read: Create New Workbook Using VBA in Excel

VBA to Unprotect a Specific Workbook

Suppose you want to unprotect a workbook named ‘Sales.xlsx’ protected without a password and then save it. You can use the VBA code below to accomplish the task:

Sub UnprotectSpecificWorkbook()
Workbooks("Sales.xlsx").Unprotect
Workbooks ("Sales.xlsx").Save
End Sub

If the workbook ‘Sales.xlsx’ is protected with a password, you can unprotect it using the subroutine below:

Sub UnprotectPasswordSpecificWorkbook()
Workbooks("Sales.xlsx").Unprotect Password:="password"
Workbooks ("Sales.xlsx").Save
End Sub

Note: Replace the password in the example subroutine with your own. If you leave out the ‘Password’ argument or supply an incorrect password, you will get the runtime error 1004.

Also read: How to Close Workbook Using VBA

VBA to Unprotect All Protected Open Workbooks

You can use the VBA code below to unprotect all open workbooks that are protected without a password:

Sub UnprotectOpenWorkbooks()
Dim wb As Workbook
For Each wb In Workbooks
       wb.Unprotect
       wb.Save
    End If
Next wb
End Sub

The code loops through each open workbook and unprotects it if it is protected without a password.

You can use the code below to unprotect all open workbooks that are protected with the same password:

Sub UnprotectPasswordOpenWorkbooks()
Dim wb As Workbook
For Each wb In Workbooks
      wb.Unprotect Password:="password"
      wb.Save
    End If
Next wb
End Sub

Note: Replace the password in the example code with your own. However, you cannot use this code if each open workbook is protected with a different password. If that is the case, you must unprotect each workbook separately.

In this tutorial, I gave you several examples of unprotecting a workbook in VBA. I hope you found the tutorial helpful.

Other Excel articles you may also like: