Page 279 - Excel Progamming Weekend Crash Course

n540629 ch20.qxd 9/2/03 9:35 AM Page 254

254 Saturday Evening

For example, the following code adds four items to a ComboBox:

ComboBox1.AddItem “West”
ComboBox1.AddItem “East”
ComboBox1.AddItem “North”
ComboBox1.AddItem “South”

You cannot add items to a ComboBox at design time, but only in code. The
form’s Initialize event procedure (covered later in this session) is usually
Note the best place to put this code.
Each item you add to a ComboBox is assigned a numerical index. The first item added has
index equal to 0, the second item has index equal to 1, and so on. When the user selects an
item in the ComboBox or enters text in the edit area, the control’s Value property returns
the text (or an empty string if no item is selected). The value returned by the ListIndex
property is as follows:

If the user has selected an item in the ComboBox’s list, the item’s index number is
returned.
If the user has not selected and item or has entered text in the edit area, -1 is
returned.
By default, a ComboBox is blank when the form is first displayed — the user must open
the form and select from the list, or enter some text. If you want the control to display an
initial value, do one of the following (after loading the control with data):

Set the control’s ListIndex property to the index of an item that has been added to
the list.
Set the control’s Value property to a string.
For example, this code loads the ComboBox and sets it to initially display Red:

ComboBox1.AddItem “Blue”
ComboBox1.AddItem “Red”
ComboBox1.AddItem “Green”
ComboBox1.AddItem “Brown”
ComboBox1.ListIndex = 1

The following program demonstrates using a ComboBox in a VBA program. Follow these
steps to create and run the program.

1. In the VBA Editor, select Insert ➪ UserForm to add a new user form to the project.
2. Change the form’s Name property to ComboBoxDemo and its Caption property to
ComboBox Demo.
3. Add a ComboBox control to the form; leave its properties as the default values.
4. Add a CommandButton control to the form and change its Caption property to
Close.
5. In the Project window, click the View Code button to display the code-editing
window for the form.