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