Popup Menus

Excel's built-in shortcut menus are included in the command bar listing created by the macro ListFirstLevelControls, which you saw earlier in this chapter. The following modified version of this macro shows only the command bars of type msoBarTypePopup, as shown in Figure 15-11.

MmmsiHlBaFTLKtjte'n

u t

A

B

C

D

1

1

CommandBar

ICorlrol

Faceld

ID

S

2

PivotChart fvlsnu

4

Field Setti&figs ^Options..

% 460 i?1S04

460 1604

5

^Refresh Data

[H 459

459

6

SHide PivotChart Field Buttons ■ 3956

3955

7

For&mulas

30254

3

Remo&ve Field

& 5416

5416

S "

Workbook tabs

10

First Level

957

957

11

All Levels

957

957

12

FacelDs

957

957

13'

Popups

957

957

14

& Sheet List

957

957

15

& Sheet List

957

957

16

Cell

1?"

Cu&t

S 21

21

10

■SCopy

a® is

19

19

&Paste

22

22

2D

Paste SiSpecial .

Cl 756

755

"21"

SJnsart

|®3181

3181

22

& Delete..

292

292

23

Clear Co&ntents

3125

3125

24

FilfSer

31402

25

S&ort

31435

26

[Jew CoSmment

a 1589

1589

27

Delete Co&mment

031592

1592

23

Sh Sow/Hide Comments

Ü1S93

1593

29

^Format Cells

Ér1 855

855

30

Pic&k From Drop-down List

1966

1966

m

&Show Phonetic Field

1614

1614

32

Name a &Range

13381

133S1

33

SiHyperlink

is, 1576

1576

H

> W tateifl . All Lei-els /FatsEs I PofNfiE1

Jk. -

The code to display the popups is shown here:

Sub ListPopups()

Dim ctl As CommandBarControl Dim cbr As CommandBar Dim iRow As Integer

If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub

'Ignore errors and freeze screen On Error Resume Next Application.ScreenUpdating = False

'Enter headings

Cells(1, 1).Value = "CommandBar" Cells(1, 2).Value = "Control" Cells(1, 3).Value = "FaceId" Cells(1, 4).Value = "ID"

'Loop through all commandbars For Each cbr In CommandBars

Application.StatusBar = "Processing Bar " & cbr.Name

'Only list popups

If cbr.Type = msoBarTypePopup Then

'Loop through controls on popup commandbar For Each ctl In cbr.Controls

Cells(iRow, 2).Value = ctl.Caption ctl.CopyFace

If Err.Number = 0 Then

ActiveSheet.Paste Cells(iRow, 3) Cells(iRow, 3).Value = ctl.FaceId End If

Cells(iRow, 4).Value = ctl.ID Err.Clear iRow = iRow + 1 Next ctl End If

Next cbr

Range("A:B").EntireColumn.AutoFit Application.StatusBar = False End Sub

The listing is identical to ListFirstLevelControls, apart from the introduction of a block If structure that processes only command bars of type msoBarTypePopup. If you look at the listing produced by ListPopups, you will find you can identify the common shortcut menus. For example, there are command bars named Cell, Row, and Column that correspond to the shortcut menus that pop up when you right-click a worksheet cell, row number, or column letter.

You might be confused about the fact that the Cell, Row, and Column command bars are listed twice. The first set is for a worksheet in Normal view. The second set is for a worksheet in Page Break Preview.

Another tricky one is the Workbook tabs command bar. This is not the shortcut that you get when you click an individual worksheet tab. It is the shortcut for the workbook navigation buttons to the left of the worksheet tabs. The shortcut for the tabs is the Ply command bar.

Having identified the shortcut menus, you can tailor them to your own needs using VBA code. For example, Figure 15-12 shows a modified Cell command bar that includes an option to Clear All.

The Clear All control was added using the following code:

Public Sub AddShortCut() Dim cbr As CommandBar Dim ctl As CommandBarControl Dim lIndex As Long

Set cbr = CommandBars("Cell")

lIndex = cbr.Controls("Clear Contents").Index Set ctl = cbr.Controls.Add(Type:=msoControlButton, _

ID:=1964, Before:=lIndex)

ctl.Caption = "Clear &All" End Sub

AddShortCut starts by assigning a reference to the Cell command bar to cbr.

If you want to refer to the Cell command bar that is shown in Page Break view in Excel 2007, you can use its Index property:

Set cbBar = CommandBars(39)

C:-mni9ndBarE. (Ism

_ n

t

G

j u

H 1 J K

L M

i

1

-

2

Arial

,10 - A* a' $ - % It J

3

s'

b

i • fc * A - "3* * S

5

6

7

Cut

8

Copy

10

Paste

11

Paste Special».,

12

13

Insert.»

14

Delete-

Í5

16

Clear All

17

■I 3

Clear Contents

19

Filter \

20

21"

Sort ►

22

Insert Comment

23

-i

24

Format Cells..,

25

26

Pick From Drop-down List,,.

27

Start New Workflow...

23

30

*

Hyperlink...

31

h

. »i

Sheetl J

■J 11» »ft-

Figure 15-12

Figure 15-12

You need to take care here, if you want code compatible with other versions of Office. In Excel 2003, the Index property of the Cell command bar in Page Break view is 32, in Excel 2000 it is 26, and in Excel 97 it is 24.

AddShortCut records the Index property of the Clear Contents control in lIndex, so that it can add the new control before the Clear Contents control. AddShortCut uses the Add method of the Controls collection to add the new control to cbBar, specifying the ID property of the built-in Edit O Clear O All menu item on the Worksheet menu bar.

The Add method of the Controls collection allows you to specify the Id property of a built-in command. The listing from ListAllControls allows you to determine that the Id property, which is the same as the FaceId property, of the Edit O Clear O All menu item is 1964.

The built-in Caption property for the newly added control is All, so AddShortCut changes the Caption to be more descriptive.

You can safely leave the modified Cell command bar in your CommandBars collection. It is not tied to any workbook and does not depend on having access to macros in a specific workbook.

0 0

Post a comment