613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

VBA Chapter 24E of 24: The List 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 list.

In the toolbox the list box  has this icon   .

Properties

- RowSource 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 mark (!), the address of the first cell, a colon and the address if 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.
- MultiSelect is set to 1 if you want the user to be able to select many values from  the list.
- Height The number of values shown in the list will depend on the height of the list box. You can set the height here or on the userform itself by stretching it. If the number of values in your RowSoOurce is greater than what can be shown in the list box a scroll bar is added automatically.
- Text should contain the value shown in the combo box when the userform is activated (Select a City, for example).
- 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 list box. 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
- 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.

613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links