Stopping Worksheets from being Reprotected

Written by Allen Wyatt (last updated December 17, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

Mark has an Excel workbook (Protected.xlsm) that is protected with a password, as are all the worksheets. It appears that all that needs to be done to bypass this security is for a user to open, at the same time as Protected.xlsm, another Excel workbook that contains a macro that protects each worksheet in the active workbook. Running the macro on Protected.xlsm effectively "reprotects" the already protected worksheets, and they are all given whatever password is specified by the macro. Therefore, any worksheet in Protected.xlsm can now be unprotected, thereby bypassing my original security. Mark wonders how he can stop this security breach from happening.

The first step in coming up with an answer is to try to recreate the problem. I created two workbooks, which I'll call Protected.xlsm (as Mark has) and BreakIt.xlsm. I did not password-protect Protected.xlsm as Mark did (more on that in a moment), but I did add a few small macros to the workbook. I placed three worksheets in the workbook, and I password-protected the first two using the password "abcd". I left the third worksheet unprotected.

In the BreakIt.xlsm worksheet I placed a single macro that will step through all the worksheets in the active workbook and protect each one:

Sub ProtSheet()
    Dim w As Worksheet
    For Each w In ActiveWorkbook.Worksheets
        w.Protect Password:="xyz", DrawingObjects:=True, _
          Contents:=True, Scenarios:=True
    Next w
End Sub

I then made Protected.xlsm the active workbook and ran the ProtSheet macro. This is the scenario that Mark described. The macro ran without error. The results were that the first two worksheets remained protected, and the original password was not changed. In other words, the protection on the worksheets was not breached—Mark's scenario was not duplicated. The third worksheet in Protected.xlsm—the one I had left unprotected to begin with—was protected by the ProtSheet macro, and the worksheet was given the new password ("xyz").

I closed Protected.xlsm without saving any changes and reopened the workbook. I then tried to set the password to nothing in the ProtSheet macro using this simple variation:

Sub ProtSheet()
    Dim w As Worksheet
    For Each w In ActiveWorkbook.Worksheets
        w.Protect Password:="", DrawingObjects:=True, _
          Contents:=True, Scenarios:=True
    Next w
End Sub

When I ran this variation on the Protected.xlsm workbook, it still ran without error. The same result occurred, however—the passwords on the originally protected worksheets were not changed or removed. Only the third worksheet (the one that I had left unprotected) was protected by the macro, and without a password.

Since I could not recreate Mark's original scenario, it made me wonder about what he was seeing. It is possible that there is some other macro running in Mark's case that is actually doing the unprotecting or the reprotecting. It is also possible that the macros behave as expected (no security breach) on my version of Excel, but they don't on Mark's version. Finally, it is also possible that because the ProtSheet macro runs, without error, that Mark mistakenly thought that the security was breached. This would be easy to assume, as one would expect the code to generate an error since it couldn't successfully password-protect a worksheet that had been previously protected with a different password.

Earlier I mentioned that I didn't password-protect the workbook as Mark had done. I didn't do this because worksheet-level protection and workbook-level protection are two entirely different things. Mark's primary problem was with worksheet-level protection being breached, not with workbook-level protection being breached. Thus, the workbook being protected was a bit of a red herring to Mark's original problem. It does, however, bring up an interesting conundrum for Mark: You have users who you trust with the password to open the workbook (so they can get past the workbook-level security) but you cannot trust them to not try to break the worksheet-level security. Your bad actor—the one trying to reprotect the worksheets—could never get to a point where that bad acting could be done without first getting past the workbook-level security. In reality, nobody should be trusted with the workbook-level password if you suspect they are bad actors wanting to break the worksheet-level protection.

If you cannot fully trust your users, however, and you think they can get past the workbook-level protection, then you could theoretically create a worksheet-level event handler that is executed every time a worksheet is activated. The handler could try to unprotect the worksheet using the correct password. If successful, then it can immediately reprotect the worksheet using your correct password. If unsuccessful, then you know the password on the worksheet was somehow changed and the macro could take whatever steps you deem necessary, such as immediately closing the workbook without saving any changes.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12452) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

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

Extracting E-mail Addresses from Hyperlinks

If you have a list of hyperlinked e-mail addresses in a worksheet, you may want to extract the addresses from those ...

Discover More

Pictures Move on their Own

Insert some pictures into a document and you may be in for a surprise--they don't necessarily stay where you put them. ...

Discover More

Specifying Default Hyperlink Text

When you insert into a cell a hyperlink that references a file on your system, the text displayed by default matches the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Protecting Worksheets from Deletion

If you share a workbook with others in your office, you will probably want to make sure that some of the worksheets don't ...

Discover More

Functioning Check Boxes in a Protected Worksheet

Want to protect a worksheet but maintain the ability to make changes to the check boxes you add to the worksheet? Here is ...

Discover More

Controlling Entry Order on Unprotected Cells

When you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a ...

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}] (all 7 characters, in the sequence shown) 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 five minus 0?

2022-12-18 16:43:23

Jay Ullius

When you verified that the worksheet passwords were not changed by re-protecting, did you also verify that the protection settings (e.g., select locked cells) could not be changed?


2022-12-17 13:04:26

David Gray

There exists in the wild at least one macro that can be run against all the worksheets in a workbook to remove all worksheet level password protection. Once the sheets are unprotected, adding a new password is trivial. I know all of this because I got the macro and used it to unprotect a reference worksheet for which I had forgotten the password.

For obvious reasons, I won't disclose the source of the macro.


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.