How to Unprotect All Sheets in Excel Using VBA (4 Examples)

Below is a dataset with four protected worksheets representing sale details for the first four months of the year. We cannot change any data on the worksheets until we unprotect them.

Unprotect All Sheets in Excel VBA

For this, we need to use the Worksheet.Unprotect method that removes protection from a sheet. The syntax of the method is:

expression.Unprotect(password)

The argument password is required to unprotect password-protected sheets. For sheets without passwords, we’ll ignore the argument.

Write Code in Visual Basic Editor

To unprotect all sheets in a workbook in Excel, we need to open and write VBA code in the Visual Basic Editor.

Steps:

Go to the Developer tab from the ribbon.
Click the Visual Basic option.

In the Visual Basic For Applications window, click the Insert dropdown to select the Module option.

Enter your code inside the module and press F5 to run it.

Method 1 – Unprotect All Sheets Without a Password in Excel VBA

Steps:

Insert the following code inside the Visual Basic Editor:

Sub UnprotectAllSheets()
     For Each wsheet In ActiveWorkbook.Sheets
         wsheet.Unprotect
      Next wsheet
End Sub

Unprotect All Sheets in Excel VBA

Press F5 to run it.

We can edit the sheets as all of them get unprotected.

Unprotect All Sheets in Excel VBA

Method 2 – Unprotect All Password Protected Sheets with Excel VBA Code

Steps:

We need to specify the password as the password argument of the Worksheet.Unprotect method.

Insert the following code inside the visual basic editor:

Sub UnprotectAllSheets()
     For Each wsheet In ActiveWorkbook.Sheets
         wsheet.Unprotect (123456)
      Next wsheet
End Sub

Press F5 to run it.

Method 3 – Unprotect All Sheets with Different Passwords in Excel VBA

In the 2nd example, we used the same password for all the worksheets in the workbook. But if each worksheet has different passwords, we need to set the corresponding password in the code individually.

Enter the following code:

Sub UnprotectAllSheets()
    Sheets("January").Unprotect ("password")
    Sheets("February").Unprotect ("password")
    Sheets("March").Unprotect ("password")
    Sheets("April").Unprotect ("password")
End Sub

Unprotect All Sheets in Excel VBA

Method 4 – Run a VBA Code to Unprotect All Sheets Except Specified Ones

Steps:

We need to use the If…Then…Else statement to check if a worksheet named January is inside the For…Each loop.

Insert the following code inside the visual basic editor:

 Sub UnprotectAllSheets()
     For Each wsheet In ActiveWorkbook.Sheets
      If wsheet.Name = "January" Then
         wsheet.Protect
    Else
         wsheet.Unprotect
    End If
      Next wsheet 
End Sub

Unprotect All Sheets in Excel VBA