Supposing you have a workbook which contains multiple worksheets, and now you need to protect all of the worksheets or some specific worksheets, normally in Excel, you only can protect the sheet one by one with the Protect Sheet function, but this method is tedious and time-consuming if there are numerous sheets need to be protected. How do you protect multiple sheets at once in Excel quickly and easily?
Protect all sheets at once with VBA code
Protect multiple sheets at once with Kutools for Excel
Unprotect multiple sheets at once with kutools for Excel
Protect all sheets at once with VBA code With the following VBA code, you can protect all the sheets of the active workbook at once with the same password, do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Protect all worksheets in a workbook 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 Sub3. Then press F5 key to run the code, and input the password for the protected sheets in the prompt box, see screenshot:
4. Click OK, and then input the password again to confirm the password.
5. Then click OK, and all the sheets have been protected with the same password.
Protect multiple sheets at once with Kutools for Excel Sometimes, you don't want to protect all the sheets in your workbook, and only want to protect some specific sheets, in this situation, the above code will not work. But with Kutools for Excel’s Protect Worksheet utility, you can protect both all sheets and specific sheets quickly and easily.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. |
After installing Kutools for Excel, please do as following steps:
1. Click Kutools Plus > Protect Worksheet, see screenshot:
2. In the Protect Worksheet dialog box, select the sheets that you want to be protected. (By default, all the worksheets within the current workbook are checked.)See screenshot:
3. And click OK, and then type and confirm the password in the Protect Worksheet dialog box. See screenshot:
4. Then click OK, the selected worksheets have been protected.
Click Protect Worksheet to know more about this feature.
Unprotect multiple sheets at once with kutools for Excel How could you unprotect multiple protected worksheets in a workbook at the same time? Of course, Kutools for Excel also provides the Unprotect Worksheet utility for you to unprotect them at once.
Please do with following steps:
1. Open your workbook which contains the protected worksheets.
2. Then click Kutools Plus > Unprotect Worksheet, see screenshot:
3. In the Unprotect Worksheet dialog box, the protected sheets are list into the list box, then click Ok button, see screenshot:
4. And then a Unprotect Worksheet dialog box pops out to remind you enter the password that you have created to protect the worksheets, see screenshot:
5. After typing the password, click Ok, all the checked worksheets in the list box have been unprotected.
Note: The worksheets must have the same password.
To know more about this Unprotect Worksheet feature.
Download and free trial Kutools for Excel Now!
Demo: Protect or Unprotect all worksheets at once with Kutools for Excel Related article:
How to unprotect multiple worksheets at once in Excel?
