Modifying a Builtin Shortcut Menu

Microsoft Excel offers 60 shortcut menus with different sets of frequently used menu options. The shortcut menu appears when you right-click on an object in the Microsoft Excel application window. Using VBA, you can return the exact number of the shortcut menus, as well as their names.

1. Enter the ShortcutMenus procedure in the current project's module, as shown below:

Sub ShortcutMenus()

Dim myBar As CommandBar Dim counter As Integer For Each myBar In CommandBars

If myBar.Type = msoBarTypePopup Then counter = counter + 1 Debug.Print counter & ": " & myBar.Name End If

Next End Sub

Notice the use of the msoBarTypePopup constant to identify the shortcut menu in the CommandBars collection. To return the names of the built-in menus, use the msoBarTypeMenuBar constant. The msoBarTypeNormal will return the names of the toolbars. When you run the ShortcutMenus procedure, the names of all shortcut menus are returned in the Immediate window. These are listed below.

Shapes Inactive Chart Excel Control Curve

Curve Node Curve Segment Pictures Context Menu OLE Object

WordArt Context Menu Rotate Mode Connector

Script Anchor Popup Canvas Popup Organization Chart Popup Diagram 56: Add Command Built-in Menus System Layout Select

Now that you know the exact names of the Excel shortcut menus, you can easily add other frequently used commands to any of these menus. Although it is easy to print a worksheet from the Print icon on the

1

Query and Pivot

20

Floor and Walls

40

2

PivotChart Menu

21

Trendline

41

3

Workbook tabs

22

Chart

42

4

Cell

23

Format Data Series

43

5

Column

24

Format Axis

44

6

Row

25

Format Legend Entry

45

7

Cell

26

Formula Bar

46

8

Column

27

PivotTable Context Menu

47

9

Row

28

Query

48

10

Ply

29

Query Layout

49

11

XLM Cell

30

AutoCalculate

50

12

Document

31

Object/Plot

51

13

Desktop

32

Title Bar (Charting)

52

14

Nondefault Drag and

33

Layout

53

Drop

34

Pivot Chart Popup

54

15

AutoFill

35

Phonetic Information

55

16

Button

36

Auto Sum

56

17

Dialog

37

Paste Special Dropdown

57

18

Series

38

Find Format

58

19

Plot Area

39

Replace Format

59

60

toolbar or by choosing File | Print, you may want to add the Print command to the shortcut menu that appears when the user right-clicks a worksheet tab. Let's see how you can add this option to the Ply menu that appears under these circumstances.

2. Enter the AddToPlyMenu procedure as shown below:

Sub AddToPlyMenu()

With Application.CommandBars("Ply") .Reset

.Controls.Add(Type:=msoControlButton, Before:=2).Caption =_

"Print... " .Controls("Print..."). End With End Sub

The Reset method used in the above procedure prevents placing the same option in the shortcut menu when you run the procedure more than once.

3. Run the AddToPlyMenu procedure. Then return to the Code window, and enter the code of the following procedure, which will be executed when you select the Print option from the shortcut menu:

Sub PrintSheet()

Application.Dialogs(xlDialogPrint).Show End Sub

4. Switch to the Microsoft Excel application window and right-click any tab. Select the Print option. You should see the same dialog box that appears when you Print using other built-in tools.

0 0

Post a comment