Adding A Menu Take

Listing 23-1 presents a better demonstration. It uses the FindControl method to attempt to locate the Help menu. If the Help menu is not found, the code adds the new menu item to the end of the Worksheet Menu Bar.

Listing 23-1: Adding the Budgeting Menu to Excel's Main Menu Bar

Sub AddNewMenu()

Dim HelpMenu As CommandBarControl Dim NewMenu As CommandBarPopup

' Find the Help Menu

Set HelpMenu = CommandBars(1).FindControl(Id:=30010)

If HelpMenu Is Nothing Then Add the menu to the end Set NewMenu = CommandBars(1).Controls _ .Add(Type:=msoControlPopup, Temporary:=True)

Else

Add the menu before Help Set NewMenu = CommandBars(1).Controls _ .Add(Type:=msoControlPopup, Before:=HelpMenu.Index, _ Temporary:=True) End If

' Add a caption

NewMenu.Caption = "&Budgeting" End Sub

The procedure in Listing 23-1 creates an essentially useless menu — it has no menu items. See "Adding a menu item to the Tools menu" later in this chapter for an example of how to add a menu item to a menu.

To use the FindControl method, you must know the ID property of the control that you're looking for. Each of Excel's built-in CommandBar controls has a unique ID property. For this example, I determined the ID property of the Help menu by executing the following statement:

MsgBox CommandBars(1).Controls("Help").ID

The message box displayed 30010, which is the value that I used as the ID argument for the FindControl method. Table 23-1 shows the ID property settings for the top-level controls in Excel's menu bars.

Table 23-1 ID PROPERTY SETTINGS FOR EXCEL'S TOP-LEVEL MENUS

Menu

ID Setting

File

30002

Edit

30003

View

30004

Insert

30005

Format

30006

Tools

30007

Data

30011

Chart

30022

Window

30009

Help

30010

0 0

Post a comment