Using VBA to Customize Shortcut Menus

In this section, I present some practical examples of VBA code that manipulates Excel's shortcut menus. These examples give you an idea of the types of things you can do with shortcut menus, and they can all be modified to suit your needs.

Resetting a shortcut menu

The Reset method restores a shortcut menu to its original, default condition. The following procedure resets the Cell shortcut menu to its normal state:

 Sub ResetCellMenu()     CommandBars("Cell").Reset End Sub

As I noted earlier, Excel has two shortcut menus named Cell. The preceding code resets only the first one (index of 36). To reset the second Cell shortcut menu, use its index number (39) instead of its name .

The following procedure resets all built-in toolbars to their original states:

 Sub ResetAll()     Dim cbar As CommandBar     For Each cbar In Application.CommandBars         If cbar.Type = msoBarTypePopup Then             cbar.Reset             cbar.Enabled = True         End If     Next cbar End Sub

Note	If your application adds items to a shortcut menu, it's better to remove the items individually when your application closes . If you simply reset the shortcut menu, it will delete customizations made by other applications.

Disabling a Shortcut Menu

The Enabled property lets you disable an entire shortcut menu. For example, you can set this property so that right-clicking a cell does not display the normal shortcut menu. The following statement disables the Cell shortcut menu:

 Application.CommandBars("Cell").Enabled = False

To re-enable the shortcut menu, simply set its Enabled property to True .

If you want to disable all shortcut menus, use the following procedure:

 Sub DisableAllShortcutMenus()     Dim cb As CommandBar     For Each cb In CommandBars         If cb.Type = msoBarTypePopup Then _           cb.Enabled = False     Next cb End Sub

Caution

Disabling shortcut menus "sticks" between sessions. Therefore, you'll probably want to restore the shortcut menus before closing Excel. To restore the shortcut menus, modify the preceding procedure to set the Enabled property to True .

Disabling shortcut menu items

You might want to disable one or more shortcut menu items on certain shortcut menus while your application is running. When an item is disabled, its text appears in light gray, and clicking it has no effect. The following procedure disables the Hide menu item from the Row and Column shortcut menus:

 Sub DisableHideMenuItems()     CommandBars("Column").Controls("Hide").Enabled = False     CommandBars("Row").Controls("Hide").Enabled = False End Sub

Adding a new item to the Cell shortcut menu

The AddToShortcut procedure that follows adds a new menu item to the Cell shortcut menu: Toggle Word Wrap. Recall that Excel has two Cell shortcut menus. This procedure modifies the normal right-click menu, but not the right-click menu that appears in Page Break Preview mode.

 Sub AddToShortCut() '   Adds a menu item to the Cell shortcut menu     Dim Bar As CommandBar     Dim NewControl As CommandBarButton     DeleteFromShortcut     Set Bar = CommandBars("Cell")     Set NewControl = Bar.Controls.Add _        (Type:=msoControlButton, _         temporary:=True)     With NewControl         .Caption = "Toggle &Word Wrap"         .OnAction = "ToggleWordWrap"         .Picture = Application.CommandBars.GetImageMso _            ("WrapText", 16, 16)         .Style = msoButtonIconAndCaption     End With End Sub

Figure 23-4 shows the new menu item displayed after right-clicking a cell.

Figure 23-4: The Cell shortcut menu with a custom menu item.

The first actual command after the declaration of a couple of variables calls the DeleteFromShortcut procedure (listed later in this section). This statement ensures that only one Toggle Word Wrap menu item appears on the shortcut Cell menu. Notice that the underlined hot key for this menu item is W, not T. That's because T is already used by the Cut menu item.

The Picture property is set by referencing the image used in the Ribbon for the Wrap Text command. Refer to Chapter 22 for more information about images used in Ribbon commands.

The macro that is executed when the menu item is select is specified by the OnAction property. In this case, the macro is named ToggleWordWrap :

 Sub ToggleWordWrap()     CommandBars.ExecuteMso ("WrapText") End Sub

This procedure simply executes the WrapText Ribbon command.

Note

When you modify a shortcut menu, that modification remains in effect until you restart Excel. In other words, modified shortcut menus don't reset themselves when you close the workbook that contains the VBA code. Therefore, if you write code to modify a shortcut menu, you almost always write code to reverse the effect of your modification.

The DeleteFromShortcut procedure removes the new menu item from the Cell shortcut menu.

 Sub DeleteFromShortcut()     On Error Resume Next     CommandBars("Cell").Controls _       ("Toggle &Word Wrap").Delete End Sub

In most cases, you want to add and remove the shortcut menu additions automatically: Add the shortcut menu item when the workbook is opened, and delete the menu item when the workbook is closed. Just add these two event procedures to the ThisWorkbook code module:

 Private Sub Workbook_Open()     Call AddToShortCut End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean)     Call DeleteFromShortcut End Sub

The Workbook_Open procedure is executed when the workbook is opened, and the Workbook_BeforeClose procedure is executed before the workbook is closed. Just what the doctor ordered.

Note	Menu items added to a shortcut menu are available in all workbooks, not just the workbook that creates the menu items.

CD	The workbook described in this section is available on the companion CD-ROM. The file-name is `add to cell shortcut.xlsm` .

Adding a submenu to a shortcut menu

The example in this section adds a submenu with three options to a shortcut menu. Actually, it adds the submenu to six shortcut menus. Figure 23-5 shows the worksheet after right-clicking a row. Each of the submenu items executes a macro that changes the case of text in the selected cells .

Figure 23-5: This shortcut menu has a submenu with three submenu items.

Finding FaceID Images

The icon that's displayed on a shortcut menu item is determined by one of two property settings:

Picture : This option lets you use an imageMso from the Ribbon. For an example, see "Adding a new item to the Cell shortcut menu," earlier in this chapter.
FaceID : This is the easiest option because the FaceID property is just a numeric value that represents one of hundreds of images.

But how do you find out which number corresponds to a particular FaceID image? Excel doesn't provide a way, so I created an application the lets you enter a beginning and ending FaceID number. Click a button, and the images are displayed in the worksheet. Each image has a name that corresponds to its FaceID value. See the accompanying figure, which shows FaceID values from 1 to 300. This workbook, named show faceids.xlsm , is available on the companion CD-ROM.

The code that creates the submenu and submenu items is as follows:

 Sub AddSubmenu() '   Adds a submenu to the six shortcut menus     Dim Bar As CommandBar     Dim NewMenu As CommandBarControl     Dim NewSubmenu As CommandBarButton     Dim cbIndex As Long     DeleteSubmenu     For cbIndex = 36 To 41         Set Bar = CommandBars(cbIndex) '       Add submenu         Set NewMenu = Bar.Controls.Add _             (Type:=msoControlPopup, _              temporary:=True)         NewMenu.Caption = "Ch&ange Case"         NewMenu.BeginGroup = True '       Add first submenu item         Set NewSubmenu = NewMenu.Controls.Add _           (Type:=msoControlButton)         With NewSubmenu             .FaceId = 38             .Caption = "&Upper Case"             .OnAction = "MakeUpperCase"         End With '       Add second submenu item         Set NewSubmenu = NewMenu.Controls.Add _           (Type:=msoControlButton)         With NewSubmenu             .FaceId = 40             .Caption = "&Lower Case"             .OnAction = "MakeLowerCase"         End With '       Add third submenu item         Set NewSubmenu = NewMenu.Controls.Add _           (Type:=msoControlButton)         With NewSubmenu             .FaceId = 476             .Caption = "&Proper Case"             .OnAction = "MakeProperCase"         End With     Next cbIndex End Sub

The AddSubmenu procedure uses a loop to modify the six CommandBar objects that have an Index between 36 and 41. These shortcut menus are the ones that appear when you right-click a cell, row, or column (different shortcut menus appear when Excel is in Page Break preview mode).

The submenu is added first, and its Type property is msoControlPopup . Then the three submenu items are added, and each has a different OnAction property.

CD	The workbook described in this section is available on the companion CD-ROM. The filename is `shortcut with submenu.xlsm` .