Page 298 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

be used to cancel the print request if set to True. Unfortunately, there is a limitation with this
event procedure: it can’t determine whether it is a print request or if the user is trying to pre-
view the workbook.
The following event procedure recalculates the entire workbook before the active workbook
is printed:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk in Worksheets
wk.Calculate
Next
End Sub

Note When testing the BeforePrint event procedure you can save time and paper by using
the Print Preview option rather than printing the workbook.

The BeforePrint event procedure can be used to ensure that certain formats have been applied
to the workbook. For example, you could adjust the page margins, set the print area, set
headings and columns that are to repeat on every page, or simply set the worksheet to print
to a single page. If there are a series of formats that are required before you print your work-
book, an event procedure including the correct page layout can be used to eliminate the time
spent reprinting workbooks with improper formatting.

BeforeClose Event

The BeforeClose event procedure is executed before the workbook is closed. This event is
often used with the Workbook_Open event procedure. For example, the Workbook_Open
procedure might open a custom menu for the workbook, and the Workbook_BeforeClose pro­
cedure would then close the custom menu. In this scenario, the custom menu would only be
visible when the workbook is open. The following event procedures demonstrate how to use
the Workbook_Open and Workbook_BeforeClose events to accomplish opening and closing
the custom menu:

Private Sub Workbook_Open
Call OpenCustomMenu
End Sub

Private Sub Workbook_BeforeClose (Cancel as Boolean)
Call CloseCustomMenu
End Sub

However, there is a problem with this scenario because Excel’s “Do you want to save changes
you made to ‘workbookname.xls’?” prompt occurs after the Workbook_BeforeClose event
procedure is executed. If the user clicks Cancel, the workbook remains open, but the custom
menu has already been closed. To avoid this problem, you can add your own code to prompt
the user to save the workbook. The following event procedure will demonstrate the modifi­
cations required for the BeforeClose procedure:
Chapter 12

272
Part 4: Advanced VBA