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.

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

• Press F5 to run it.

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

Read More: Excel VBA: How to Unprotect Excel Sheet without Password

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.

Read More: How to Unprotect Excel Sheet with Password Using VBA

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

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

• Press F5 to run it.

The sheet January is not editable as it is protected.

But the other sheets are editable.

Things to Remember

• The password argument of the Unprotect method is case–sensitive.

Download the Practice Workbook

Download this workbook to practice.

Related Articles

• Excel VBA: Protect Workbook with Password
• Excel VBA: Unprotect Workbook with Password
• Excel VBA: Unprotect Workbook without Password
• Excel VBA to Protect Sheet but Allow to Select Locked Cells