Microsoft Office Tutorials and References
In Depth Information
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
subsequent 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.
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"
The OnAction routine shown here is called whenever the user selects an item from the
dropdown 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.
Public Sub TestControl()
If .Parameter = "Dropdown Box #1" Then