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
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.
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",
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"
Search JabSto ::

Custom Search