Page 407 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Command Bars

Set cb = c.Controls.Add(msoControlButton)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Menu button #2"
cb.Parameter = "Menu #2"
cb.OnAction = "ThisWorkbook.TestPopup"
Set cp = c.Controls.Add(msoControlPopup)
cp.Parameter = "Popup #1"
cp.Caption = "Submenu choices"
Set cb = cp.Controls.Add(msoControlButton)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Submenu button #1"
cb.Parameter = "Sub menu #1"
cb.OnAction = "ThisWorkbook.TestPopup"

Set cb = cp.Controls.Add(msoControlButton)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Submenu button #2"
cb.Parameter = "Submenu #2"
cb.OnAction = "ThisWorkbook.TestPopup"
End If

c.ShowPopup
End Sub

To create the pop-up menu, a new command bar object of type msoBarPopup must be created.
Then the individual controls can be added to the pop-up menu.
Simply adding msoControlButton objects creates the traditional list of menu items. If you
wish to display submenu items, you need to add an msoControlPopup item. This item adds an
item to the menu with an arrow indicating that there are submenus available. It also acts as a
container for the submenu items, which can be added using the same technique as was used
for the pop-up bar.

Finally, once the command bar has been initialized, it may be shown using the ShowPopup
method. Remember that you need to execute this method each time you want to display the
pop-up menu because once the user selects an item from the menu, the menu will disappear. Chapter 17
Typically, you display a pop-up menu in response to a user action such as running a particu­
lar macro, handling a particular condition with a larger Excel application, or in responding to
a key sequence.

381