Microsoft Office Tutorials and References
In Depth Information
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 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 (read-only): returns an object reference to a CommandBar
object representing the menu for the pop-up.
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
trapping 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.
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",
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"