Danny's VBA Scripts

Popup Menus

• Get Everify To Uncover The Truth About Someone
• How To Make Your Own Wind Generator
• Designer brand names and fashions

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