Page 438 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

The ListBox Control
The ListBox control displays a list of information to the user, from which the user can select
one or more entries. The list of items that can be selected are arranged into a series of rows
and columns. The number of columns is controlled by the ColumnCount property. This
Chapter 19
value contains the number of columns that are available. By default, this property is set to 1,
meaning that a single column will be displayed. If you set this property to -1, all available
columns will be displayed (up to a total of 10).
When the ColumnHeads property is True, the first row in the list is used as the header for the
column. Notice that this row can’t be selected. The ColumnWidths property specifies the size
of each column. The sizes for all column widths are contained in a single string formatted as
a set of numbers separated by semicolons (;). Each number specifies the width of the column
in points. You may also specify widths in centimeters (cm) and inches (in). Decimal points
are allowed. Specifying a value of zero as the width of a particular column means that that
particular column won’t be displayed.
The ListCount property contains the number of rows in the list. The List property accesses
the list of items and takes two parameters, the row number and the column number. Both
parameters start with zero and have a maximum value of one less than the ColumnCount or
ListCount properties. If you reference the List property without any arguments, you can copy
a two-dimensional array of values to the property in a single statement.

The following routine shows how you might use the List and ColumnCount properties to
initialize a list box control:

Private Sub UserForm_Initialize()

Dim MyList(10, 2) As String
DimiAsLong
MyList(0, 0) = "Line"
MyList(0, 1) = "Column A"
MyList(0, 2) = "Column B"
Fori=1To 10
MyList(i, 0) = "#" & FormatNumber(i, 0)
MyList(i, 1) = "A" & FormatNumber(i, 0)
MyList(i, 2) = "B" & FormatNumber(i, 0)
Next i

ListBox1.ColumnCount = 3
ListBox1.List = MyList

End Sub

412