Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Shortcut Menus and Events

The examples in this section demonstrate various shortcut-menu programming techniques used in conjunction with events.

Adding and deleting menus automatically

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

Private Sub Workbook_Open() Call ModifyShortcut End Sub

To return the shortcut back to its state before the modification, use a procedure such as the following. This procedure is executed before the workbook closes , and it executes the RestoreShortcut procedure (not shown here):

Private Sub Workbook_BeforeClose(Cancel As Boolean) Call RestoreShortcut End Sub

A problem could arise, however, if the workbook is not saved when the user closes it. Excel's "Do you want to save the changes?" 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 RestoreShortcut End Sub

This procedure determines whether the workbook has been saved. If it has been saved, no problem; the RestoreShortcut 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 restoring the shortcut menu.

Disabling or hiding shortcut menu items

When a menu item is disabled, its text appears in a faint shade of gray, and clicking it has no effect. When a menu item is hidden, it does not appear on the shortcut menu. You can, of course, write VBA code to enable or disable shortcut menu items. Similarly, you can write code to hide shortcut menu items. The key, of course, is tapping into the correct event.

The following code, for example, disables the Change Case shortcut menu item (which was added to the Cells menu) when Sheet2 is activated. This procedure is located in the code module for Sheet2:

Private Sub Worksheet_Activate() CommandBars("Cell").Controls("Change Case").Enabled = False End Sub

To enable the menu item when Sheet2 is deactivated, add this procedure. The net effect is that the Change Case menu item is available at all times except when Sheet2 is active.

Private Sub Worksheet_Deactivate() CommandBars("Cell").Controls("Change Case").Enabled = True End Sub

To hide the menu item rather than disable it, simply access the Visible property instead of the Enabled property.

Creating a context-sensitive shortcut menu

You can create an entirely new shortcut menu and display it in response to a particular event. The code that follows creates a shortcut menu named MyShortcut and adds six menu items to it. These menu items have their OnAction property set to execute a simple procedure that displays one of the tabs in the Format Cells dialog box (see Figure 23-6).

Figure 23-6: A new shortcut menu appears only when the user right-clicks a cell in the shaded area of the worksheet.

Sub CreateShortcut() Set myBar = CommandBars.Add _ (Name:="MyShortcut", Position:=msoBarPopup, _ Temporary:=True) ' Add a menu item Set myItem = myBar.Controls.Add(Type:=msoControlButton) With myItem .Caption = "&Number Format..." .OnAction = "ShowFormatNumber" .FaceId = 1554 End With ' Add a menu item Set myItem = myBar.Controls.Add(Type:=msoControlButton) With myItem .Caption = "&Alignment..." .OnAction = "ShowFormatAlignment" .FaceId = 217 End With ' Add a menu item Set myItem = myBar.Controls.Add(Type:=msoControlButton) With myItem .Caption = "&Font..." .OnAction = "ShowFormatFont" .FaceId = 291 End With ' Add a menu item Set myItem = myBar.Controls.Add(Type:=msoControlButton) With myItem .Caption = "&Borders..." .OnAction = "ShowFormatBorder" .FaceId = 149 .BeginGroup = True End With ' Add a menu item Set myItem = myBar.Controls.Add(Type:=msoControlButton) With myItem .Caption = "&Patterns..." .OnAction = "ShowFormatPatterns" .FaceId = 1550 End With ' Add a menu item Set myItem = myBar.Controls.Add(Type:=msoControlButton) With myItem .Caption = "Pr&otection..." .OnAction = "ShowFormatProtection" .FaceId = 2654 End With End Sub

After the shortcut menu is created, you can display it by using the ShowPopup method. The following procedure, located in the code module for a Worksheet object, is executed when the user right-clicks a cell:

Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Excel.Range, Cancel As Boolean) If Union(Target.Range("A1"), Range("data")).Address = _ Range("data").Address Then CommandBars("MyShortcut").ShowPopup Cancel = True End If End Sub

If the active cell is within a range named data when the user right-clicks, the MyShortcut menu appears. Setting the Cancel argument to True ensures that the normal shortcut menu is not displayed. Note that the mini toolbar is not displayed.

You can also display this shortcut menu without even using the mouse. Create a simple procedure and assign a shortcut key by using the Options button in the Macro dialog box.

Sub ShowMyShortcutMenu() ' Ctrl+Shift+M shortcut key CommandBars("MyShortcut").ShowPopup End Sub