VBA Lesson 3-7: The Combo Boxes in VBA for Excel
If you are looking for a drop-down list to use on a regular worksheet see the much easier and user friendly Excel drop-down lists in the website on Excel.
In the toolbox the combo box has this icon .
For most controls including the VBA for Excel combobox there are general properties that allow you to set the font, the color of the font, the color of the background, the type of background, the type of border and other design features. Using the 3 windows in the Visual Basic Editor you will see the following properties in the "Property" window when the combo box is selected.
The other interesting properties of the combobox in VBA for Excel are:
- Name: Set the "Name" property of all the controls that you will refer to in your VBA procedures. I encourage you to use prefixes and some upper case letters in these names (cbxCity, txbCityName). Be as descriptive as possible to make your code clearer. The prefixes that I use are: command button (cmb), labels (lbl), combo boxes (cbx), text boxes (txb), list boxes (lbx), check boxes (ckb), radio button (rdb), toggle buttons (tbt), frames (fra), tab strips (tsp), multi pages (mpg), scroll bars (scb), spin buttons (spb), images (img) and ref edits (rfe). The controls that particularly need to be well named are the controls which eventual values you will be using in your procedures like the text boxes, the list boxes, the combo boxes, the option buttons and the check boxes.
For example: Range("A1").Value = cbxCityName.Value
- RowSource: No complex programming to submit a list of values to feed the combo box. The values that should appear in the drop-down list of the combo box are submitted in the RowSource property. For example Sheet1!A1:A12 will feed the list with the values residing in cells A1 to A12 of the sheet with the Caption: "Sheet1" . The rules to submit the RowSource property is the caption of the sheet where the list resides followed by an exclamation point (!), the address of the first cell, a colon and the address of the last cell. IMPORTANT NOTE: if there is a space or a special character in the caption of the sheet where the list resides you must surround it with simple quotes like in 'This sheet'!A1:A12.
- ListRows: is the number of values shown in the drop-down list. If you show less than the complete list a scroll bar is added automatically.
- MatchRequired: is by default set to false meaning that the user can submit any value in the combo box. If you want the user to be limited to the values in the list set this property to True.
- Text: can contain the value shown in the combo box when the userform is activated (Select a City, for example). If there is no value set for this property the combo box is empty. You can tell the user what you want him to enter in it with a label above the combo box or with the ControlTipText property.
- ControlTipText: which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the combo box.
- ColumnCount: is the number of columns of values that you want shown in the drop-down list. For example if you want to show part number and part name in the list you will submit a RowSource like Sheet1!A1:B12 with the part numbers in column A and the part names in column B. In this situation the ColumnCount property is set at 2.
- ColumnWidth: is the width of all the columns shown in the drop-down list of the combo box.
- BoundColumn: is the column from which the value is drawn for the final value of the combo box. For example if the part number is in column A of the RowSource and the part name is in column B of the RowSource when the user select a value only column A or column B will become the final value of the combo box . So if you set the value of BoundColumn to 1 the part number becomes the final value. If you set BoundColumn to 2 the part number becomes the final value.
Cascading Combo Box
You want the second combo box to offer a choice of values that differs based on the first combo box. We call it cascading combo boxes. Here is an example with step by step explanations: "vba-tutorial-combo-boxes.xls"
As you have read earlier most of the VBA code concerning userforms and controls is linked to events related to the userform itself and the command buttons.
There are 17 events related to the combo box (Double click on the userform name in the project window of the VBE and then double click on the combo box.
You can develop code for each of these events.
Developed and Presented by PLI Consultant Inc