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.


Download Practice Workbook

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


4 Suitable Examples to Unprotect All Sheets Using VBA in Excel

Let’s say in our sample workbook, there are 4 worksheets protected that represent sale details for 1st four months of a 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 passwordprotected 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 Excel Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic For Applications window, click the Insert dropdown to select the New Module

Now put your code inside the visual code editor 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

Unprotect All Sheets in Excel VBA
Output: Now we can edit the sheets as all of them get unprotected.

Unprotect All Sheets in Excel VBA

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 Quick Tricks)


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

Unprotect All Sheets in Excel VBA

Read More: How to Unprotect Excel Sheet with Password (2 Quick Methods)


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

Unprotect All Sheets in Excel VBAOutput: The sheet January is not editable as it is protected. 

Unprotect All Sheets in Excel VBA

But the other sheets are editable.

Unprotect All Sheets in Excel VBA

Read More: How to Unprotect Excel Sheet without Password (4 Easy Ways)


Things to Remember

  • The password argument of the Unprotect method is casesensitive.

Conclusion

Now, we know how to unprotect all sheets in Excel VBA with 4 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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo