Excel VBA: Unprotect All Sheets (4 Suitable Examples)

This article illustrates 4 suitable examples to unprotect all sheets in a workbook in Excel using VBA. Excel allows the feature to protect sheets with or without passwords to protect them from editing, deleting, and even copying accidentally or deliberately. In case, we have a bunch of protected sheets in a workbook, 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.

Unprotect All Sheets in Excel Using VBA: 4 Suitable Examples

Let’s say in our sample workbook, there are 4 protected worksheets that represent sale details for 1st four months of a 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 just 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. Follow the steps to open the Visual Basic Editor and write some code there.

• 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.

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

1. Unprotect All Sheets Without Password in Excel VBA

Task: Unprotect all worksheets in a workbook that aren’t password protected.

Code: Insert the following code inside the Visual Basic Editor and press F5 to run it.

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

Output: Now we can edit the sheets as all of them get unprotected.

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

2. Unprotect All Password Protected Sheets with Excel VBA Code

Task: Unprotect all sheets in a workbook that are password protected.

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

Code: Insert the following code inside the visual basic editor and press F5 to run it.

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

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

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 there are different passwords for each of the worksheets, then we need to individually set the corresponding password in the code. The code will look like the following.

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

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

Task: We want to unprotect all the worksheets except specific ones. In this illustration, we’ll leave the sheet named January while unprotecting all other sheets in the workbook.

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

Code: Insert the following code inside the visual basic editor and press F5 to run it.

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

Output: 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 Practice Workbook

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

Conclusion

Now, we know how to unprotect all sheets in Excel VBA with 4 different examples. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below. Goodbye!

Related Articles

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