If you want to protect or unprotect all worksheets in Excel, you only can do each worksheet manually. Is there any way to have a Macro protect or unprotect multiple worksheets at once in Excel quickly and easily?
Protect All Worksheets At Once
Protect All Worksheets Use InputBox
Sub ProtectAllWorksheetsWithInputbox()
'Step 1: Declare your variables
Dim ws As Worksheet
Dim Pwd As String
'Step 2: enter your password to protect all worksheets
Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
'Step 3: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 4: Protect and loop to next worksheet
ws.Protect Password:=Pwd
Next ws
End Sub
Protect All Worksheets With Specific Password
Sub ProtectAllWorksheets()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 3: Protect all worksheets with specific password and loop to next worksheet
ws.Protect Password:="excel2013"
Next ws
End Sub
Unprotect All Worksheets At Once
Unprotect All Worksheets Use InputBox
Sub UnProtectAllWorksheetsWithInputbox()
'Step 1: Declare your variables
Dim ws As Worksheet
Dim Pwd As String
'Step 2: enter your password to unprotect all worksheets
Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
'Step 3: Start looping through all worksheets
For Each ws In Worksheets
'Step 4: Unprotect all worksheets and loop to next worksheet
ws.Unprotect Password:=Pwd
Next ws
'Step 5: Error handling
If Err <> 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If
On Error GoTo 0
End Sub
Unprotect All Worksheets With Specific Password
Sub UnprotectAllWorksheets()
'Step 1: Declare your variables
Dim ws As Worksheet
'Step 2: Start looping through all worksheets
For Each ws In ActiveWorkbook.Worksheets
'Step 3: Unprotect all worksheets with specific password and loop to next worksheet
ws.Unprotect Password:="excel2013"
Next ws
End Sub
Unprotect Specific Worksheet With Specific Password
Sub UnprotectSpecificWorksheet()
'Unprotect specific worksheet with specific password
Sheets("Sheet1").Unprotect Password:="excel2003"
Sheets("Sheet2").Unprotect Password:="excel2007"
Sheets("Sheet3").Unprotect Password:="excel2010"
Sheets("Sheet4").Unprotect Password:="excel2013"
End Sub
How to Use
To use this macro, you can copy and paste it into a standard module:
- Activate the Visual Basic Editor by pressing ALT+F11.
- Right-click the project/workbook name in the Project window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module.
I need it to check off Formal columns and Format rows too in the protect sheet box since that setting never saves in the worksheet and I need them to be able to do that. How do I add that to your Macro?
Thanks to you my 20 years using "=" functions has been upgraded to VBA Scripts. 😉
Very good and detailed work- Thank you. It is nice to come back from being away from coding in VBA and have such good examples and help in doing things I am sure would take me a while to remember how to do.