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

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

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

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.

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.

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.

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.