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
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:
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
You've protected and saved your worksheet with explicit instructions that you be allowed to insert and delete rows. But ...
Discover MoreExcel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a ...
Discover MoreWhen you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments