Page 406 - Microsoft Office Excel 2003 Programming Inside Out

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

Inside the routine, the Parameter property associated with the control is examined to see if it
was the drop-down list that was created earlier. If it was, the index of currently selected item
is used to extract the appropriate item from the list.

Pop-Up Controls

Pop-up controls are used to display hierarchical information, such as a drop-down menu or
list of icons. Unlike the other types of command bar controls in Excel, pop-up controls
include two unique properties. (See Table 17-8.)
Table 17-8. Unique Properties of the CommandBarPopup Object
Property/Method Description

CommandBar Property (read-only): returns an object reference to a CommandBar
object representing the menu for the pop-up.
Controls Property (read-only): returns an object reference to a
CommandBarControls object containing the command bar controls
for a pop-up menu.

Displaying a Pop-Up

Displaying pop-up menus is a useful task for many VBA programs. The following routine
shows how to create and display a pop-up menu. This routine begins by disabling error trap-
ping by using the On Error Resume Next statement. Then the routine tries to get an object ref­
erence to the Excel2k2 VBA Popup command bar. If the command bar object doesn’t exist, the
variable c will be set to Nothing. Without the On Error statement, any attempt to reference a
nonexistent command bar would trigger a run-time error.

Sub ShowCommandPopup()

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

On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Popup")
If c Is Nothing Then
Set c = Application.CommandBars.Add("Excel2k3 VBA Popup", _
Chapter 17
msoBarPopup, False, True)
c.Enabled = True
c.Visible = True
Set cb = c.Controls.Add(msoControlButton)
cb.Style = msoButtonIconAndCaption
cb.Caption = "Menu button #1"
cb.Parameter = "Menu #1"
cb.OnAction = "ThisWorkbook.TestPopup"

380