How to Protect/Unprotect Multiple Worksheets At Once in Excel

If you have a workbook contains multiple protected worksheets with the same password, and now you need to unprotect them for modifying some information. Maybe, you can unprotect the sheets one by one with Unprotect Sheet in Excel, but this will waste a lot of time when there are multiple sheets need to be unprotected. Here, I will introduce you some quick ways to unprotect multiple protected sheets.
Microsoft Excel • Vba Script

For each of the code sections below:

1. Go to Tools > Macros > Visual Basic Editor.

2. Right-click on your workbook and select Insert > Module, and paste the following code in the Module Window.

3. Then press F5 key to run the code, and enter the password that you have applied for the protected sheets. (the protected sheets must have the same password)

4. Click OK, and the protected sheets have been protected/unprotected at once.

Protect Multiple Worksheets At Once

Sub protect_all_sheets()
top:
pass = InputBox("password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "you made a boo boo"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."
End Sub

Unprotect Multiple Worksheets At Once

Sub unprotect_all_sheets()

    On Error GoTo booboo
    unpass = InputBox("password")
    For Each Worksheet In ActiveWorkbook.Worksheets
        Worksheet.Unprotect Password:=unpass
    Next
    Exit Sub

booboo: MsgBox "There is s problem - check your password, capslock, etc."

End Sub

Written by https://www.extendoffice.com/documents/excel/1153-excel-protect-multiple-sheets-at-once.html#a1
https://www.extendoffice.com/documents/excel/1154-excel-unprotect-multiple-sheets.html#a1

Posted by fbrefere001 on Friday July 14, 2017