Stopping Worksheets from being Reprotected

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.

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.