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 April 19, 2014)

3

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

Sequentially Numbered Labels

A common task in Word is to create labels. This tip presents two approaches you can use when you need to create labels that ...

Discover More

Searching for All

When you are working on a worksheet (particularly a large one), you may want to search for and possibly copy information from ...

Discover More

Creating Default Formatting for Workbooks and Worksheets

Not satisfied with the way that default workbooks and worksheets look in Excel? You can easily create your own defaults for ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 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

Setting Program Window Size in a Macro

The macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel appears ...

Discover More

Removing a Macro from a Shortcut Key

When you assign a macro to a shortcut key, you make it easy to run the macro without ever removing your hands from the ...

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 7 - 0?

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.