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 th gets its data from a worksheet range.

■ tl

F

0 H

1

noduti

bk

fci-Llu:!

;

iitytiAdlAi

W-]

Vr- Uumnluirn I h<B«ir Oirat

i

F.™ii mm«

■v u

m

MadlldlWI^ll

IV £

1

1 Vie«

.

1

-ipj*

HhJ

M

-.cir.

V.'-J

fm

r

SI

He:

■I tic-*!

W-&

t*M fit

SL-1

... SL-i.

i

S-l

Til

5-3

fm

w

m

|

i]

Plbl

: 11»

IX.

1

i:

if i-nrVit !®c

i-Kd)

Till

11

ii

it

IT

' ' SHrtTI . 1

!

H

Figure 14-14: This ListBox displays a three-column list with column headers. CD-ROM

Figure 14-14: This ListBox displays a three-column list with column headers. CD-ROM

This example, named listbox multicolumnl .xlsm , is available on the companion CD-ROM.

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 headers, 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 (1/72 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:

10 0;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 this using a 12-row * 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 .

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

Private Sub UserForm_Initialize() ' Fill the list box

Data(i, 1) = Format(DateSerial(2007, i, 1), "mmmm") Next i

Data(i, 2) = Day(DateSerial(2007, i + 1, 1) - 1) Next i

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

CD-ROM

This example is available on the companion CD-ROM. The file is named % listbox multicolumn2.xlsm

Note

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

+1 -1

Responses

Post a comment