Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

How to Protect All Worksheets in Excel

Wondering, in Excel Protect Sheet dialog box (Review -> Protect Sheet) you can set protection on a single worksheet. If you want to protect all the worksheets, and seems to be more trouble. Use VBA to handle it very easily.
Right-click on any sheet tab, select the "View Code" and paste the code at the cursor:

Sub ProtectAllSheet()
Dim MySheet As Worksheet

For Each MySheet In Worksheets
MySheet.Protect
Next

MsgBox "Protect All Worksheets Successful"
End Sub

Position the cursor anywhere in the code, press F5 to run the code.

If you want to set the password for worksheet protection, write behind to Protect passwords (MySheet.Protect), separated by spaces, password in double quotation marks if the password is 111:
MySheet.Protect "111"

Code is as follows:

Sub ProtectAllSheet()
Dim MySheet As Worksheet

For Each MySheet In Worksheets
MySheet.Protect "111"
Next

MsgBox "Protect All Worksheets Successful"
End Sub

If you forget the worksheet-protection password, you cannot unprotect the worksheet.

Solutions: With this Tool can remove sheet protection.


How to Unprotect All Worksheets in Excel

If you want to unprotect all sheets, Replace Protect -> Unprotect, and if there is a password, also written behind a password, for example:

Code is as follows:

Sub UnprotectAllSheet()
Dim MySheet As Worksheet

For Each MySheet In Worksheets
MySheet.Unprotect "111"
Next

MsgBox "Unprotect All Worksheets Successful"
End Sub

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved