Microsoft Office Tutorials and References
In Depth Information
Private Sub Workbook_BeforeClose(Cancel as Boolean)
Dim Msg as String
If Me.Saved Then
Msg = "Do you want to save the changes you made to " & Me.Name & "?"
Ans = MsgBox (Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Me.Save = True
Cancel = True
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 Tr ue , 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.
The events for the Wo rksheet 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 Wo rksheet 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 Wo rksheet event procedure, follow these steps:
Double-click the Wo rksheet object named Sheet1 (Sheet1) to activate it.
Click the Object drop-down list, and select the Worksheet option.
Click the Procedure drop-down list, and select the desired event.