Microsoft Office Tutorials and References
In Depth Information
Getting Acquainted with Workbook-Level Events
This example, named hide columns before printing.xlsm , is available on the
For more information about OnTime events, see “The OnTime event,” later in this chapter.
The BeforeClose event
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 might use the Workbook_
Open procedure to add shortcut menu items for your workbook and then use the Workbook_
BeforeClose procedure to delete the shortcut menu items when the workbook is closed. That
way, the custom menu is available only when the workbook is open.
Unfortunately, the Workbook_BeforeClose event isn’t implemented very well. For example, if
you attempt to close a workbook that hasn’t been saved, Excel displays a prompt asking whether
you want to save the workbook before closing, as shown in Figure 19-5. The problem is, the
Workbook_BeforeClose event has already occurred by the time the user sees this message. If
the user cancels, your event-handler procedure has already executed.
Figure 19-5: When this message appears, Workbook_BeforeClose has already done its thing.
Consider this scenario: You need to display custom shortcut menus when a particular workbook
is open. Therefore, your workbook uses a Workbook_Open procedure to create the menu items
when the workbook is opened, and it uses a Workbook_BeforeClose procedure to remove
the menu items when the workbook is closed. These two event-handler procedures follow. Both
of these call other procedures, which aren’t shown here.
Private Sub Workbook_Open()
Private Sub Workbook_BeforeClose(Cancel As Boolean)