Adding and deleting menus automatically

If you need a menu to be created when a workbook is opened, use the Workbook_Open event. The following code, stored in the code module for the ThisWorkbook object, executes the CreateMenu procedure (not shown here):

Private Sub Workbook_Open()

Call CreateMenu End Sub

To delete the menu when the workbook is closed, use a procedure such as the following. This procedure is executed before the workbook closes, and it executes the DeleteMenu procedure (not shown here).

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu End Sub

A problem could arise, however, if the workbook is not saved when the user closes it. 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 your custom menu has already been deleted!

One solution to this problem is to bypass Excel's prompt and write your own code in the Workbook_BeforeClose procedure to ask the user to save the workbook. The following code demonstrates how:

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 End Sub

This procedure determines whether the workbook has been saved. If it has, no problem; the DeleteMenu procedure 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 normally shows. If the user clicks Yes, the workbook is saved, the menu is deleted, and the workbook is closed. If the user clicks No, the code sets the Saved property of the Workbook object to True (without actually saving the file) and deletes the menu. If the user clicks Cancel, the BeforeClose event is canceled, and the procedure ends without deleting the menu.

0 0

Post a comment