Handling The Workbook Beforeclose Event

Category: General VBA | [Item URL]

The BeforeClose event occurs before a workbook is closed. This event is often used in conjunction with a Workbook_Open event handler. For example, you can use the Workbook_Open procedure to initialize things, and use the Workbook_BeforeClose procedure to "clean up" things before the workbook is closed.

If you attempt to close a workbook that hasn't been saved, Excel displays a prompt asking if you want to save the workbook before closing. A potential problem can arise because by the time the users sees this message, the BeforeClose event has already occurred -- which means that your Workbook_BeforeClose subroutine has already executed.

Consider this scenario: You need to display a custom menu when a particular workbook is open. Therefore, your workbook uses a Workbook_Open procedure to create the menu when the workbook is opened, and it uses a Workbook_BeforeClose procedure to remove the menu when the workbook is closed. These two event-handler procedures (which must be in the code module for the ThisWorkbook object) are listed below.

Private Sub Workbook_Open()
    Call CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteMenu
End Sub

But, as I noted above, Excel's "save workbook before closing" prompt occurs after the Workbook_BeforeClose event-handler runs. So if the user clicks Cancel, the workbook remains open -- but the custom menu item has already been deleted!

One solution to this problem is to by-pass Excel's prompt and write your own code in the Workbook_BeforeClose procedure to ask the user to save the workbook. The code below demonstrates. This procedure must be in the code module for the ThisWorkbook object.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
        Msg = "Do you want to save the changes you made to "
        Msg = Msg & Me.Name & "?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        Select Case Ans
            Case vbYes
                Me.Save
            Case vbNo
                Me.Saved = True
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    End If
    Call DeleteMenu 'Change this to your own subroutine
End Sub

This procedure first determines if the workbook has been saved. If so, no problem -- the DeleteMenu subroutine is executed and the workbook is closed. But if the workbook has not been saved, the procedure displays a message box that duplicates the one Excel would normally show.

If the user clicks Yes the workbook is saved, the menu is deleted, and the workbook is closed. If the user click No, the code sets the Saved property of the Workbook object to True (but doesn't actually save the file), and deletes the menu. If the user clicks Cancel, the BeforeClose event is canceled and the subroutine ends without deleting the menu.