The ComboBox control is used to store a list of values. It combines the functionality of a list and a text box.
Unlike the ListBox, only one item is displayed in the ComboBox at a time. It does not support multiple selection.
The ComboBox control allows the user to enter a value through its text box, similar to the TextBox control.
Its properties, such as ListIndex, ListCount, List, and its methods Clear, RemoveItem, and AddItem, are analogous to those of the ListBox control.
In addition, the ComboBox has several unique properties, listed in Table.
Table. Properties of ComboBox
| Property | Description |
| DropButtonStyle | Sets the appearance of the drop button. Valid constants: fmDropButtonStylePlain (button without symbols), fmDropButtonStyleArrowDisplays (button with arrow), fmDropButtonStyleEllipsis (button with ellipsis), fmDropButtonStyleReduce (button with line). |
| ListRows | Sets the number of items displayed in the drop-down list. |
| MatchRequired | Values: True (input must match one of the listed items, i.e. the text box function is disabled) or False (otherwise). |
| MatchFound | Values: True (an item in the ComboBox matches the input value) or False (otherwise). |
ComboBox, alphabetical data entry, and the Collection object
Let us create a simple application where data (e.g., surnames) entered through the ComboBox’s text box is automatically sorted alphabetically.
As soon as data is entered and the key is pressed, the items are displayed in the ComboBox in alphabetical order. When the window is closed, all data from the ComboBox is written into the worksheet cells.
For this application, we will use the Collection object, which is a convenient dynamic storage for other objects.
The Collection object has only one property and three methods:
- Count — returns the number of elements.
- Item — returns an element.
- Add — adds a new element.
- Remove — deletes an element.
The code provided serves both as a good example of working with the Collection object (insertion of new items is determined using a binary search algorithm) and as a demonstration of entering data into the ComboBox from its text box.
So, create a form, place a ComboBox on it, and in the form module type the required code.
Adding and deleting data in a ComboBox
Now let us consider an application where new items (different from those already present in the list) can be added to the ComboBox through its text box.
Thus, entering a duplicate item (an item already in the list) is not allowed.
This makes the data entry approach different from the one described in the previous section.
Additionally, items can be deleted from the ComboBox, and the entire list can be cleared.
Initially, the list is populated with data from a cell range. If a new item is entered in the ComboBox text box, it is also added to the underlying range that fills the ComboBox. When an item is deleted from the list, it is also removed from the range.
In both cases, the size of the range changes, since rows are added or removed. Therefore, the range used to populate the ComboBox must be redefined constantly.
Constructing the application
Create a form with a ComboBox and three buttons. Set their property values in the Properties window as shown in Table.
Table. Property values set in the Properties window
| Control | Property (Name) |
| ComboBox | cboNames |
| Button | cmdAdd |
| Button | cmdDelete |
| Button | cmdClear |
The list will be filled with data (e.g., surnames) from a column starting at cell A1.
- Clicking the Add button adds a new element from the ComboBox text box to the list.
- Clicking the Delete button removes the selected item from the list.
- Clicking the Clear button clears the entire list.
