Listing Excel menu information

The ListMenuInfo procedure, which follows, might be instructive for those who will be customizing Excel menus. It displays the caption for each item (menu, menu item, and submenu item) on the Worksheet Menu Bar.

Sub ListMenuInfo()

Dim row As Integer

Dim Menu As CommandBarControl

Dim MenuItem As CommandBarControl

Dim SubMenuItem As CommandBarControl row = 1

On Error Resume Next

For Each Menu In CommandBars(1).Controls For Each MenuItem In Menu.Controls

For Each SubMenuItem In MenuItem.Controls Cells(row, 1) = Menu.Caption Cells(row, 2) = MenuItem.Caption Cells(row, 3) = SubMenuItem.Caption row = row + 1 Next SubMenuItem Next MenuItem Next Menu End Sub

Notice that this code uses On Error Resume Next to avoid the error message that appears when the procedure attempts to access a submenu item that doesn't exist.

Figure 23-1 shows a portion of the ListMenuInfo procedure's output.

Referencing the CommandBars Collection

The CommandBars collection is a member of the Application object. When you reference this collection in a regular VBA module, you can omit the reference to the Application object because it is assumed. For example, the following statement (contained in a standard VBA module) displays the name of the first element of the CommandBars collection:

MsgBox CommandBars(1).Name

When you reference the CommandBars collection from a code module for a ThisWorkbook object, you must precede it with a reference to the Application object, like this:

MsgBox Application.CommandBars(1).Name

A B

c

D

E

&lnsert

«.Name

«.Create...

15'

SJnsert

&Name

«Apply...

155

&lnsert

&Name

8. Label...

156

8Jnsert

Co&mment

15/

&lnsert

Ink «Annotations

158

SJnsert «.Picture

«.Clip Art...

159

SJnsert

^Picture

«.From File...

16(

8Jnsert

«.Picture

From ¿Scanner or Camera...

161

SJnsert

^Picture

Ink StDrawing and Writing

16?

8Jnsert

«.Picture

8AutoShapes

163

8Jnsert

«.Picture

«.Word Art...

164

SJnsert

«.Picture

«.Organization Chart

Ifcfc

SJnsert

Dia«.gram...

166

SJnsert

«.Object...

167

8Jnsert

Hyperl8.ink...

16f

F&ormat

C&ells...

16?

F&ormat

«.Row

H&eiqht...

170

F format

«.Row

SAutoFit

171

F«.ormat

«.Row

«.Hide

\n

F format

«.Row

«.Unhide

17:

F format

«.Column

«.Width...

174

F8.ormat

8.Column

SiAutoFit Selection

175

F&ormat

«.Column

«.Hide

176

FSiOrmat

«.Column

«.Unhide

Ml

F «.ormat

«.Column

«.Standard Width...

176

F format

S&heet

«.Rename

mM

I"

V m|\ Sheet 1 /

hi l

Figure 23-1: A portion of the output from the ListMenuInfo procedure.

A workbook that contains this procedure is available on the companion CD-ROM.

Menu-Making Conventions

You might have noticed that menus in Windows programs typically adhere to some established conventions. No one knows where these conventions came from, but you should follow them if you want to give the impression that you know what you're doing. When you modify menus, keep the following points in mind:

♦ Tradition dictates that the File menu is always first and that the Help menu is always last.

♦ Menu text is always proper (or title) case: That is, the first letter of each word is uppercase except for minor words such as the, a, and and.

♦ A top-level menu does not cause any action. In other words, each menu must have at least one menu item.

♦ Menu items are usually limited to three or fewer words.

♦ Every menu item should have a hot key (an underlined letter) that's unique within the menu.

♦ A menu item that displays a dialog box is followed by an ellipsis (...).

♦ Menu item lists should be kept relatively short. Sometimes, submenus provide a good alternative to long lists. If you must have a lengthy list of menu items, use separator bars to separate items into logical groups.

♦ If possible, disable menu items that are not appropriate in the current context. In VBA terminology, to disable a menu item, set its Enabled property to False.

♦ Some menu items serve as toggles. When the option is on, the menu item is preceded by a check mark.

0 0

Post a comment