Al Arafat Siddique
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.
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 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
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 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
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
Output: The sheet January is not editable as it is protected.Â
But the other sheets are editable.
Read More: How to Unprotect Excel Sheet without Password (4 Easy Ways)
Things to Remember
- The password argument of the Unprotect method is case–sensitive.
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.
