Handling The Workbook 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 can use the Workbook_Open procedure to initialize things, and use the Workbook_BeforeClose procedure to "clean up" things before the workbook is closed.
If you attempt to close a workbook that hasn't been saved, Excel displays a prompt asking if you want to save the workbook before closing. A potential problem can arise because by the time the users sees this message, the BeforeClose event has already occurred -- which means that your Workbook_BeforeClose subroutine has already executed.
Consider this scenario: You need to display a custom menu when a particular workbook is open. Therefore, your workbook uses a Workbook_Open procedure to create the menu when the workbook is opened, and it uses a Workbook_BeforeClose procedure to remove the menu when the workbook is closed. These two event-handler procedures (which must be in the code module for the ThisWorkbook object) are listed below.Private Sub Workbook_Open() Call CreateMenu End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteMenu End Sub
But, as I noted above, Excel's "save workbook before closing" prompt occurs after the Workbook_BeforeClose event-handler runs. So if the user clicks Cancel, the workbook remains open -- but the custom menu item has already been deleted!
One solution to this problem is to by-pass Excel's prompt and write your own code in the Workbook_BeforeClose procedure to ask the user to save the workbook. The code below demonstrates. This procedure must be in the code module for the ThisWorkbook object.Private Sub Workbook_BeforeClose(Cancel As Boolean) If Not Me.Saved Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Me.Save Case vbNo Me.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If Call DeleteMenu 'Change this to your own subroutine End Sub
This procedure first determines if the workbook has been saved. If so, no problem -- the DeleteMenu subroutine is executed and the workbook is closed. But if the workbook has not been saved, the procedure displays a message box that duplicates the one Excel would normally show.
If the user clicks Yes the workbook is saved, the menu is deleted, and the workbook is closed. If the user click No, the code sets the Saved property of the Workbook object to True (but doesn't actually save the file), and deletes the menu. If the user clicks Cancel, the BeforeClose event is canceled and the subroutine ends without deleting the menu.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks