Microsoft Office Tutorials and References
In Depth Information
Getting Acquainted with Workbook-Level Events
As I note earlier, Excel’s Do you want to save . . . prompt displays after the
Workbook_BeforeClose event handler runs. So, if the user clicks Cancel , the workbook
remains open, but the custom menu items have already been deleted.
One solution to this problem is to bypass Excel’s prompt and write your own code in the
Workbook_BeforeClose procedure to ask the user to save the workbook. The following code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
If Me.Saved = False Then
Msg = “Do you want to save the changes you made to “
Msg = Msg & Me.Name & “?”
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Cancel = True
Me.Saved = True
This procedure checks the Saved property of the Workbook object to determine whether the
workbook has been saved. If so, no problem — the DeleteShortcutMenuItems procedure is
executed, and the workbook is closed. But, if the workbook hasn’t been saved, the procedure
displays a message box that duplicates the one that Excel would normally show. The effect of
clicking each of the three buttons is
h Yes: The workbook is saved, the shortcut menu items are deleted, and the workbook is
h No: The code sets the Saved property of the Workbook object to True (but doesn’t
actually save the file), deletes the menu items, and closes the file.
h Cancel: The BeforeClose event is canceled, and the procedure ends without deleting
the shortcut menu items.
A workbook with this example is available on the companion CD-ROM. The file is
named workbook_beforeclose workaround.xlsm .