Page 405 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Command Bars

Items are added to the list using the Add method. You can optionally specify the relative posi­
tion of the item to be added. All items, starting with the item at the specified location, will be
moved one position toward the end of the list. Items are removed from the list by using the
RemoveItem method and specifying the relative position of the item to be removed. All sub-
sequent items will be moved one position closer to the start of the list.

Using a Combo Box

Combo boxes and drop-down lists are great ways to allow a user to quickly select from a list
of values. The following routine creates a new drop-down list control with three items. The
Parameter property is used to identify this particular control, and the OnAction property
specifies a macro that will be processed when a value from the drop-down list is selected.

Sub AddCommandCombo()

Dim c As CommandBar
Dim cb As CommandBarComboBox
Set c = Application.CommandBars("Excel2k3 VBA")

Set cb = c.Controls.Add(msoControlDropdown)
cb.Style = msoComboNormal
cb.AddItem "Item 1"
cb.AddItem "Item 2"
cb.AddItem "Item 3"
cb.Parameter = "Dropdown Box #1"
cb.OnAction = "ThisWorkbook.TestControl"

End Sub
The OnAction routine shown here is called whenever the user selects an item from the drop-
down list. The With statement is used to simplify the reference to the active control that trig­
gered the macro, which is referred to by the CommandBars.ActionControl property. Remem­
ber that you have to run both the AddFloatingCommandBar routine and the
AddCommandCombo routines in order to test the OnAction event.
Chapter 17
Public Sub TestControl()

With Application.CommandBars.ActionControl
If .Parameter = "Dropdown Box #1" Then
MsgBox .List(.ListIndex)

End If
End With

End Sub

379