Note: The other languages of the website are Google-translated. Back to English

How to protect multiple worksheets at once in Excel?

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


arrow blue right bubble 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 Sub

3. Then press F5 key to run the code, and input the password for the protected sheets in the prompt box, see screenshot:

doc-protect-multiple-sheets1-1

4. Click OK, and then input the password again to confirm the password.

doc-protect-multiple-sheets2-2

5. Then click OK, and all the sheets have been protected with the same password.


arrow blue right bubble 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:

doc-protect-multiple-sheets4-4

3. And click OK, and then type and confirm the password in the Protect Worksheet dialog box. See screenshot:

doc-protect-multiple-sheets5-5

4. Then click OK, the selected worksheets have been protected.

Click Protect Worksheet to know more about this feature.


arrow blue right bubble 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:

doc-protect-multiple-sheets7-7

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:

doc-protect-multiple-sheets8-8

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!


arrow blue right bubble Demo: Protect or Unprotect all worksheets at once with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related article:

How to unprotect multiple worksheets at once in Excel?


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (21)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Great--so how to UNprotect them all at once? I'm an amateur, so I need more info.
This comment was minimized by the moderator on the site
[quote]Great--so how to UNprotect them all at once? I'm an amateur, so I need more info.By Kristin Dzugan[/quote] :-) You can easily unprotect them as http://www.extendoffice.com/product/kutools-for-excel/excel-unprotect-multiple-sheets.html
This comment was minimized by the moderator on the site
Sub unprotect_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 Next For Each s In ActiveWorkbook.Worksheets s.Unprotect Password:=pass Next Exit Sub oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets B4 running this Macro." End Sub
This comment was minimized by the moderator on the site
Thank you so much :-)
This comment was minimized by the moderator on the site
As Kristin said, how do you unprotect them all at once?
This comment was minimized by the moderator on the site
So, how do I unprotect without kutools?
This comment was minimized by the moderator on the site
[quote]So, how do I unprotect without kutools?By cz[/quote] You can unprotect the worksheet without Kutools for Excel. You just need to right click on the sheet tab which you want to unprotect and click Unprotect Sheet command, and then enter your password. :-)
This comment was minimized by the moderator on the site
when I imput the code it comes up with "run time error 1004?? highlighting the following in yellow... s.Protect Password:=pass
This comment was minimized by the moderator on the site
I'm suddenly having the 1004 error as well. I have been using the macro for months but now it won't run.
This comment was minimized by the moderator on the site
Hi, I was having this issue too. Turns out it can't run if you have all sheets selected in the workbook. Try selecting just one sheet and then running.
This comment was minimized by the moderator on the site
I'm suddenly having the error 1004 issue. Have been using the macro for months now it randomly fails.
This comment was minimized by the moderator on the site
I am suddenly having the 1004 error as well. Anyone find a solution?
This comment was minimized by the moderator on the site
when i activated this VBA, hyperlink doesn't work. how can i make them work? tnx :)
This comment was minimized by the moderator on the site
Thank you! This is awesome, I was even able to modify the VBA code so that I could unprotect all sheets at once! :) Sub unprotect_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 Next For Each s In ActiveWorkbook.Worksheets s.Unprotect Password:=pass Next Exit Sub oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets B4 running this Macro." End Sub
This comment was minimized by the moderator on the site
Hey, I need some help, the above code was very helpful, but it locks the sort (auto filter as well) can you please provide code to solve that problem and also where it would be inserted. Thank you in advance.
This comment was minimized by the moderator on the site
I have chart tabs and worksheets in my Excel 2013 workbook. The VBA coding above works for the protecting the worksheets but not the separate chart tabs. How do i password protect the multiple chart tabs and multiple worksheets in my workbook? Thanks!
This comment was minimized by the moderator on the site
Hi I used this code now I it wont accept my password to get back into my spreadsheet !!
This comment was minimized by the moderator on the site
How do I mask the password using the VBA method? I can not find this information via google or F1 help files. Thanks in advance!
This comment was minimized by the moderator on the site
Thanks for the wonder full tool to protect & unprotect all the sheets at a time. Please guide me I am able to unprotect all sheets in my work book at a time but when I run protect all sheets VBA Code then only first 4 sheets are protected & rest or left unprotected. Please help me to sort out the issue.
This comment was minimized by the moderator on the site
I am trying to use this in a shared workbook but I get a Microsoft Visual Basic error "Run-time error '1004": Application-defined or object-defined error" I am just looking for suggestions on how to get around this. Any help would be appreciated
This comment was minimized by the moderator on the site
Occurs when sheet is already locked
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations