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.
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
Similar Readings
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!
