VBA add values to multi column listbox

Working with multicolumn List Box controls

A normal ListBox has a single column for its contained items. You can, however, create a ListBox that displays multiple columns and [optionally] column headers.

Figure 14-14 shows an example of a multicolumn ListBox that gets its data from a worksheet range.

To set up a multicolumn ListBox that uses data stored in a worksheet range, follow these steps:

1. Make sure that the ListBox's ColumnCount property is set to the correct number of columns.

2. Specify the correct multicolumn range in the Excel worksheet as the ListBox's RowSource property.

3. If you want to display column heads like the ListBox in Figure 14-14, set the ColumnHeads property to True. Do not include the column headings on the worksheet in the range setting for the RowSource property. VBA will instead automatically use the row directly above the first row of the RowSource range.

4. Adjust the column widths by assigning a series of values, specified in points [V?2 of one inch] and separated by semicolons, to the ColumnWidths property. For example, for a three-column list box, the ColumnWidths property might be set to the following text string:

100;40;30

5. Specify the appropriate column as the BoundColumn property. The bound column specifies which column is referenced when an instruction polls the ListBox's Value property.

To fill a ListBox with multicolumn data without using a range, you first create a two-dimensional array and then assign the array to the ListBox's List property. The following statements demonstrate, using a 12-row x 2-column array named Data. The two-column ListBox shows the month names in column 1 and the number of the days in the month in column 2 [see Figure 14-15]. Notice that the procedure sets the ColumnCount property to 2.

Private Sub UserForm_Initialize[] ' Fill the list box

Data[i, 1] = Format[DateSerial[2001, i, 1], "mmmm"] Next i

Data[i, 2] = Day[DateSerial[2001, i + 1, 1] - 1] Next i

ListBox1.ColumnCount = 2 ListBox1.List = Data End Sub

Figure 14-15: A two-column ListBox filled with data stored in an array.

There appears to be no way to specify column headers for the ColumnHeads property when the list source is a VBA array.

Continue reading here: Using a List Box to select worksheet rows

Was this article helpful?