Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Visually Showing a Protection Status.

Visually Showing a Protection Status

by Allen Wyatt
(last updated June 20, 2015)

1

Todd has developed a workbook used by others. To prevent data from being ruined, he's protected the worksheet as well as the workbook. The problem is, Todd sometimes forgets to protect the worksheet and workbook after making changes. He is wondering if there is a way to create a visual indicator that shows whether the worksheet/workbook is currently protected or unprotected.

Of course, the easiest way to check to see if something is unprotected is to just start looking at the menu choices available. If the full range of choices is there, then the worksheet and workbook are unprotected. If there are significant parts that are unavailable ("grayed out"), then protection is turned on.

Another easy solution is to create a user-defined function that returns a value indicating whether the workbook or worksheet are protected. The following will do the trick:

Function WksProtected(rng As Range) As String
    Application.Volatile
    If rng.Parent.ProtectContents Then
        WksProtected = "Protected"
    Else
        WksProtected = "Not Protected"
    End If
End Function
Function WkbProtected(rng As Range) As String
    Application.Volatile
    If rng.Parent.Parent.ProtectStructure Then
        WkbProtected = "Protected"
    Else
        WkbProtected = "Not Protected"
    End If
End Function

To use the macros, just include formulas like the following anywhere in the worksheet:

=WksProtected(A1)
=WkbProtected(A1)

The result of the formulas is either "Protected" or "Not Protected," depending on the state of the worksheets and workbook. You could use conditional formatting to highlight the cells based on what is returned by the functions.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3172) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Visually Showing a Protection Status.

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

Inserting Endnotes

Endnotes appear at either the end of a section or the end of an entire document. It is just as easy to insert an endnote into ...

Discover More

Sorting by Five Columns

Excel allows you to sort but up to three columns, but you may want to sort by more than that. This tip provides ways you can ...

Discover More

Age Calculation with Fields

People don't normally think of using fields to do any calculations. Even so, you can use fields to perform a simple ...

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 (MENU)

Locking All Non-Empty Cells

Need to make sure that your worksheet is locked, with only the blank cells accessible to editing? You can do this easily by ...

Discover More

Protecting a Single Worksheet

Excel allows you to protect your worksheets easily—and that includes if you need to protect only a single worksheet out ...

Discover More

Selecting Combo Boxes in Locked Worksheets

Once you protect a worksheet, you may run into problems with any combo boxes that the worksheet contains. This is a simple ...

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 for this tip:

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. 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 three minus 1?

2015-07-03 11:07:10

Tom Bates

This has happened to me too. What I've done is add code to the BeforeSave Workbook event that runs through all the sheets and ensures that they're protected. If there are any that should be which are not, I pop up a msgbox to remind me.

It's a fair amount of code, but it has gone into my "toolbox" so I can reuse it, which I do frequently.

Tom


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.

Links and Sharing