Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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


Protect multiple worksheets with same password at once:

With Kutools for Excel’s Protect Worksheet and Unprotect Worksheet, you can protect or unprotect all worksheets within a workbook with the same password at once.

doc-protect-multiple-sheets-9

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


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 Enterprise > Protect Worksheet, see screenshot:

doc-protect-multiple-sheets3-3

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.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days

Please do with following steps:

1. Open your workbook which contains the protected worksheets.

2. Then click Enterprise > Unprotect Worksheet, see screenshot:

doc-protect-multiple-sheets6-6

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 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related article:

How to unprotect multiple worksheets at once in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    George · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Geetha · 3 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Mayhem · 3 years ago
    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!
  • To post as a guest, your comment is unpublished.
    James MacPherson · 3 years ago
    Hi I used this code now I it wont accept my password to get back into my spreadsheet !!
  • To post as a guest, your comment is unpublished.
    S. Brown · 3 years ago
    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!