Working with Shortcut Menus

You can't use the Customize dialog box to remove or modify shortcut menus. The only way to customize shortcut menus is to use VBA.

A shortcut menu appears when you right-click an object. Excel has lots of shortcut menus. You can pretty much right-click anything and get a shortcut menu. To work with a shortcut menu, you need to know the shortcut menu's Caption or Index. This information isn't available in the Help system, but you can use the following procedure to generate a list of all shortcut menus and the Index and Caption for each:

Sub ListShortCutMenus() Dim Row As Long Dim Col As Long Dim cbar As CommandBar Row = 1

For Each cbar In CommandBars

If cbar.Type = msoBarTypePopup Then Cells(Row, 1) = cbar.Index Cells(Row, 2) = cbar.Name For Col = 1 To cbar.Controls.Count Cells(Row, Col + 2) = _ cbar.Controls(col).Caption Next Col Row = Row + 1 End If Next cbar End Sub

This example, available on this book's Web site, produces something like that shown in Figure 20-7.

JttNG/

Be careful when you modify shortcut menus. Like normal menus, changes to shortcut menus are saved between sessions. Therefore, you probably want to reset the shortcut menus when your application ends.

Figure 20-7:

A listing of all shortcut menus, plus the menu items in each.

Figure 20-7:

A listing of all shortcut menus, plus the menu items in each.

0 0

Post a comment