Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Unprotecting Groups of Worksheets.

Unprotecting Groups of Worksheets

by Allen Wyatt
(last updated October 27, 2017)

5

Excel allows you to protect and unprotect worksheets. The purpose, of course, is to allow others to use your workbook, but not to modify certain cells within each worksheet.

Since protection is done at a worksheet level, it can be major pain to step through each worksheet in a workbook and either protect or unprotect them. If you have 25 worksheets, you must activate each worksheet, do the protect or unprotect, and move on to the next one.

A less time-consuming method of protecting each worksheet in a workbook is to use a macro to do the actual work. The following macro will do the trick:

Sub ProtectAllSheets()
    Dim ws As Worksheet
    Dim sOrigSheet As String
    Dim sOrigCell As String
    Dim J As Integer

    Application.ScreenUpdating = False
    sOrigSheet = ActiveSheet.Name
    sOrigCell = ActiveCell.Address

    For Each ws In Worksheets
        ws.Select
        ws.Protect Password:="Password"
    Next ws

    Application.GoTo Reference:=Worksheets("" _
      & sOrigSheet & "").Range("" & sOrigCell & "")
    Application.ScreenUpdating = True
End Sub

The macro to unprotect all the worksheets is only slightly different:

Sub UnProtectAllSheets()
    Dim ws As Worksheet
    Dim sOrigSheet As String
    Dim sOrigCell As String
    Dim J As Integer

    Application.ScreenUpdating = False
    sOrigSheet = ActiveSheet.Name
    sOrigCell = ActiveCell.Address

    For Each ws In Worksheets
        ws.Select
        ws.Unprotect Password:="Password"
    Next ws

    Application.GoTo Reference:=Worksheets("" _
      & sOrigSheet & "").Range("" & sOrigCell & "")
    Application.ScreenUpdating = True
End Sub

While these macros will work just fine, there are a couple of caveats. First, you need to make sure that the Password variable in each macro is set to the proper password for your worksheets. (This assumes, of course, that all the worksheets use the same passwords.) The second caveat is that since the macro has to include the password, the overall security of your workbook may be compromised—anyone that can display the macros will know what the passwords are for your workbooks.

As a solution to this last problem, you could modify the macros so that they ask for a password to use in their work. The following would be the version of the macro that protects worksheets:

Sub ProtectAllSheetsPass()
    Dim ws As Worksheet
    Dim sOrigSheet As String
    Dim sOrigCell As String
    Dim J As Integer
    Dim sPWord As String

    Application.ScreenUpdating = False
    sOrigSheet = ActiveSheet.Name
    sOrigCell = ActiveCell.Address

    sPWord = InputBox("What password?", "Protect All")
    If sPWord > "" Then
        For Each ws In Worksheets
            ws.Select
            ws.Protect Password:=sPWord
        Next ws
    End If
    Application.GoTo Reference:=Worksheets("" _
      & sOrigSheet & "").Range("" & sOrigCell & "")
    Application.ScreenUpdating = True
End Sub

The macro displays an input box asking for the password. The same password is then used to protect every worksheet in the workbook. The same sort of change can be done to the macro that unprotects all the worksheets.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13075) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Unprotecting Groups of Worksheets.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Determining a Column Width

When laying out your document, you may wonder what width you should use for your text. An old typographers trick may help to ...

Discover More

Placing Formula Results in a Comment

Excel won't allow you to directly or automatically insert the results of a formula into a cell's comment. You can, however, ...

Discover More

Displaying the First Worksheet in a Macro

When creating macros, you often have to know how to display individual worksheets. VBA provides several ways you can display ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Stopping a Checked Box from being Unchecked

When creating user forms for use in Excel, you are provided with a range of controls you can add, including check boxes. If ...

Discover More

Running a Macro when a Worksheet is Deactivated

When you change from one worksheet to another, you may want to have Excel automatically run a macro for the worksheet you are ...

Discover More

Bypassing the BeforeClose Event

Hold down the Shift key as you open a workbook, and Excel bypasses any "startup macros" that may be in the workbook. If you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2017-11-09 23:31:35

Karen

This all works fine unless you have hidden sheets in the workbook and then it crashes out and leaves you at the sheet prior to the hidden ones.


2017-10-27 06:22:16

Henk

Bryan:
Could you, for the benefit of learning, show us how you would save the cell as a range so that you don't need to save the worksheet. How would you then use it.

Thank you


2014-04-23 07:00:44

Bryan

Glenn: The only difference between Exit Sub and End Sub is that End Sub has to be the last line of the subroutine, whereas Exit Sub can go anywhere in the subroutine.

The reason I like to use "If X then GoTo EXIT_SUB" instead of "If X Then Exit Sub" is more procedural than anything else. I got into the habit of using the EXIT_SUB label in my procedures so that if I need to put some sort of exit procedures into the sub, I can add it after the label without having to change every Exit Sub call. (For instance, in this case I realized I forgot to close the error handling... I should add "On Error GoTo 0" in between EXIT_SUB and Exit Sub). For something this small it's irrelevant, but I like to keep consistent so I don't have to think about it later on.

Lastly, if there is no error checking, I usually do just make the EXIT_SUB label the very last code line in the procedure and let End Sub take care of the exiting. In this case I put it before the error handling, because otherwise the code would keep reading and you'd get to the error handling procedure when there wasn't an error. Now it will get to Exit Sub first, and it can only get to the error handling if an error actually occurred.


2014-04-21 11:04:22

Glenn Case

Bryan:

Good discussion. I'm trying to learn the subtleties of VBA and this raised a couple interesting questions:

For your last procedure, can you explain the difference between Exit Sub and End Sub in this case, and why you added the Exit Sub line rather than just placing the EXIT_SUB label just prior to the End Sub statement?

And why GoTo EXIT_SUB rather than just ...Then Exit Sub?

Thanks...


2014-04-21 09:03:32

Bryan

Few things about the macros:

1) You've got an extra variable (J) dimensioned in every macro that isn't used. Also, saving the address of the cell instead of saving the cell as a range is really sloppy. If you'd just save as a range then you don't even need to save the worksheet, because this is already a property of the range object. Then you don't need to reconvert it back into a range at the end of your macro. (I didn't write code for this because both of these variables become moot in my second comment).

2) If you don't select the sheets before you protect them, you can shorten each macro to just 4 lines:

Sub ProtectSheets()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Protect Password:="Password"
Next ws

End Sub

Sub UnprotectSheets()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Unprotect "Password"
Next ws

End Sub

This is yet another in a long list of reasons to avoid selecting worksheets/cells at all costs.

3) There's no error handling. My 30 seconds of testing shows that as long as the password is correct there's no problem protecting a protected sheet or unprotecting an unprotected sheet, but you will throw an error if the password is incorrect when unprotecting.

4) There's no provision for what happens if someone presses "cancel" on the InputBox. In ProtectSheets, all of your sheets will be protected with a blank password, and in UnprotectSheets you will likely get an error.0


Knowing all of the above, here's a more robust version of UnprotectSheets. It keeps trying the same password on each sheet until it doesn't work, then it prompts for a new password.

Sub UnprotectSheets2()

Dim ws As Worksheet
Dim strPW As String

strPW = InputBox("Enter password.")
If Len(strPW) = 0 Then GoTo EXIT_SUB

On Error GoTo ERR_HANDLER

For Each ws In Worksheets
ws.Unprotect strPW
Next ws

EXIT_SUB:
Exit Sub

ERR_HANDLER:
strPW = InputBox("Enter password for worksheet " & ws.Name)
If Len(strPW) = 0 Then GoTo EXIT_SUB
Resume

End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.