Excel unprotect sheet in a BeforeClose script not working when called by ThisWorkbook.Close

Question

I have an excel workbook that protects and hides all worksheets but one before the workbook is closed. On that one worksheet it is supposed to unprotect the sheet, clear out some cell values, then reprotect the sheet.

When I close the workbook with the X in the top corner the sub runs just fine. When I try to use ThisWorkbook.Close the sub does not actually unprotect the sheet so I get a Run-time error '1004' when it tries to clear the cell values.

I need the .Close method to work for another script which will close the workbook after x amount of time.

Before Close Script

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Unprotect Password:=pw
For Each Worksheet In ThisWorkbook.Worksheets
    Worksheet.Protect Password:=pw
    If Worksheet.Name = "Control Tab" Then
        With Worksheet
            .Unprotect Password:=pw
            .Cells(24, 4).Value = ""
            .Cells(24, 5).Value = ""
            .Cells(24, 6).Value = ""
            .Cells(24, 7).Value = ""
            .Cells(24, 8).Value = ""
            .Cells(24, 9).Value = ""
            .Cells(24, 10).Value = ""
            .Cells(24, 11).Value = ""
            .Cells(24, 12).Value = ""
            .Cells(24, 13).Value = ""
            .Cells(24, 14).Value = ""
            .Cells(24, 15).Value = ""
            .Cells(24, 16).Value = ""
            .Protect Password:=pw
        End With
    Else
        Worksheet.Visible = 0
    End If
Next
ThisWorkbook.Protect Password:=pw

ThisWorkbook.Save
End Sub

Button to close workbook (for testing)

Sub Button1_Click()
    ThisWorkbook.Close
End Sub

I've tried to search for a reason why there might be a difference between problematically and manually closing a workbook on google and stackoverflow but haven't found any insight as to why the script won't unprotect the sheet. Any help would be greatly appreciated.

The sheet stays protected so the users can not delete the formulas or macro buttons on that sheet by mistake. — Rokomo

Unknown Unknown · Accepted Answer · 2018-10-25T00:00:46

First off, I wouldn't put that code in Workbook_BeforeClose; I would put it in a Workbook_BeforeSave instead and use,

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    if not thisworkbook.saved then _
        ThisWorkbook.Save

End Sub

... as the method of closing out the workbook. I'm not going to write paragraphs of white-paper jargon as to why; it just seems like a better method to me and still allows you to force the workbook to a certain condition before it is closed. With your method, it is theoretically possible to manually save the workbook in another condition then make a copy of the freshly saved workbook through file explorer without closing the workbook.

Finally, why isn't the Control Tab worksheet protected with the UserInterfaceOnly:=True argument? This would allow you to make any modification you want through VBA without unprotecting/modifying/reprotecting that particular worksheet while still restricting the user.

Run this once then remove all VBA references to unprotect/protect the "Control Tab" worksheet (unless you specifically want to open it up to the user).

sub RunOnce()
    with thisworkbook.worksheets("Control Tab")
        .Unprotect Password:=pw
        .Protect Password:=pw, UserInterfaceOnly:=True
    end with
end sub

Any and all VBA coding that affects the Control Tab worksheet will run. Caveat: there may be some coded modification that require Application.DisplayAlerts = False if you want to avoid distracting message dialogs altogether.

Excel unprotect sheet in a BeforeClose script not working when called by ThisWorkbook.Close

1 Answers