Page 299 - Microsoft Office Excel 2003 Programming Inside Out

Understanding and Using Events

Private Sub Workbook_BeforeClose(Cancel as Boolean)
Dim Msg as String
If Me.Saved Then
Call DeleteMenu
Exit Sub
Else
Msg = "Do you want to save the changes you made to " & Me.Name & "?"
Ans = MsgBox (Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Me.Save
Call DeleteMenu
Case vbNo
Me.Save = True
Call DeleteMenu
Case vbCancel
Cancel = True
End Select
End If
End Sub

This event procedure checks the Saved property of the Workbook object to determine if the
workbook has been saved. If the workbook has been saved, the DeleteMenu procedure is
executed and the workbook is closed. However, if the workbook has not been saved, the pro­
cedure will display the normal Excel warning. If the user selects Yes, the workbook is saved,
the menu is deleted, and the workbook is closed. If the user selects No, the workbook is not
saved but the Saved property is set to True, the procedure deletes the menu and closes the
workbook. If the user clicks Cancel, the BeforeClose event is canceled and the procedure ends
without deleting the menu.

Worksheet Events

The events for the Worksheet object are some of the most useful events at your disposal in the
world of Microsoft Excel. As you will see, the events for the Worksheet object occur when the
worksheet is activated or the user changes the content of a cell. The Worksheet events are also
activated by default, but as mentioned in the “Enabling and Disabling Events” section earlier,
you can disable or deactivate the events.
To display the event procedures for a worksheet, use the Visual Basic Editor. Expand the
desired project in the project window and double-click the worksheet to activate it. All event
procedures associated with the worksheet will be displayed in the code window on the right
side of the window.

To create a new Worksheet event procedure, follow these steps:
1 Double-click the Worksheet object named Sheet1 (Sheet1) to activate it.
2 Click the Object drop-down list, and select the Worksheet option. Chapter 12
3 Click the Procedure drop-down list, and select the desired event.

273
Part 4: Advanced VBA