Page 297 - Microsoft Office Excel 2003 Programming Inside Out

Understanding and Using Events

The NewSheet event procedure is often used to assist users by automating their work. For
example, The Garden Company’s accountant has asked her assistant to document when new
worksheets have been added to the Income Statement.xls file. They decided to input the date
that the worksheet was created in cell A1. To ensure that the creation date is added to new
worksheets, the following NewSheet procedure was created:

Private Sub Workbook_NewSheet(ByVal Sh as Object)
If TypeName(Sh) = "Worksheet" Then _
Range("A1") = "Worksheet created on " & Now()
End Sub

BeforeSave Event

The BeforeSave event procedure is executed before the workbook is saved. This procedure
uses two arguments, the SaveAsUI and Cancel. The SaveAsUI argument is used to identify if
the Save As dialog box will be displayed, and the Cancel argument can be used to cancel the
save operation. For example, the following event procedure will prompt users before they
save the workbook to verify that they want to save the changes made:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
a = MsgBox ("Do you want to save the changes to this workbook?", vbYesNo)
If a = vbNo Then Cancel = True
End Sub
When the user saves the workbook, the Workbook_BeforeSave procedure is executed. If the
save operation brings up Excel’s Save As dialog box, the SaveAsUI variable is True. If the
BeforeSave procedure sets the Cancel argument to True, the file will not be saved.

Deactivate Event
The Deactivate event occurs when the workbook is deselected. The following procedure
arranges all open windows when the workbook is deactivated:

Private Sub Workbook_Deactivate()
Application.Windows.Arrange xlArrangeStyleTiled
End Sub

Caution The Deactivate event occurs when a workbook is deselected; however, it’s also
triggered when a new workbook is opened or when the workbook is closed. Be careful
when using this event; thoroughly test your procedure to ensure you are achieving the
required result.

Chapter 12
BeforePrint Event

The BeforePrint event is executed when the user tries to print the workbook or uses Print
Preview. The request is transferred to the event procedure as a Cancel argument, which can

271
Part 4: Advanced VBA