Page 408 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

Adding Items to an Existing Menu
You can integrate your application into Excel’s normal menu structures by adding your own
menu items to the standard Excel menu items. For example, the following routine adds an
About menu item for a custom VBA application:

Sub AddMenuItem()

Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup

On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then
Set cp = c.Controls("&Help")

If Not cp Is Nothing Then
Set cb = cp.Controls.Add(msoControlButton)
cb.Style = msoButtonCaption
cb.Caption = "About My VBA App"
cb.OnAction = "ThisWorkbook.TestMenu"
End If

End If
End Sub

The first step is to locate the command bar where you want to add the new menu item.
Because all menu items in Excel are located in the Worksheet menu bar, the easiest way to
begin is to locate this command bar. Then within this command bar, an object reference to
the &Help pop-up control is located.
Finally, using the &Help pop-up control’s Controls collection, a new control button can be
added to the end of the list for your application’s About message. The OnAction property
specifies the routine that will be called to display the about message.

In this chapter, you learned that command bars combine the functions of menu items and
toolbars into a single unified system. You learned about the CommandBar object (which can
represent a menu or a toolbar) and the CommandBars collection, which contains references
to all of the CommandBar objects in your workbook. Then you learned how to add various
Chapter 17
command bar controls, such as pop-up menus, buttons, and combo boxes to your command
bars. Finally, you learned how to define a routine that will respond to an event fired by a com­
mand bar control, giving you application an opportunity to perform the task associated with
the command bar control. These facilities allow you to create macros and add-ins that appear
as if they were part of Excel itself.

382