 |
|
|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
When you have a ListBox it can display more than one Column of data at a time as well as more than one Row. When you set the BoundColumn for a ListBox it will determine the Column that is returned as the current value for a ListBox. Lets say we had a ListBox with three Columns of data and 10 Rows. If we set the BoundColumn to 2 and then selected the fifth row the current value of the ListBox would be whatever is on Row 5 - Column 3. The reason it would be Column 3 and not Column 2 is because the first Column is always 0 (zero) as is the Row.
This where we can set the number of Columns to display in our ListBox. Setting this to 0 (zero) means no Columns will be displayed. To display all available Columns you set this to -1 If the ListBox is *unbound to a **datasource the limit for ColumnCount is 10 ie; 0-9
Describes a control that is not related to a worksheet cell. In contrast, a bound control is a data source for a worksheet cell that provides access to display and edit the value of a control
The location of data to which a control is bound, for example, a cell in a worksheet. The current value of the data source can be stored in the Value property of a control. However, the control does not store the data; it only displays the information that is stored in the data source.
Must be either TRUE or FALSE (Boolean). Setting it to TRUE will display a single row of Column headings in your ListBox. These cannot be selected if the first row of data is used as the ColumnHeads. When using a range of cells to fill a ListBox (ListFillRange described below) the Row immediately above the first Row is used as the ColumnHeads. This means if the ListFillRange was A2:D50 the range A1:D1 would be your ColumnHeads. If the ListFillRange was A1:D50 the ColumnHeads would be Excels Column headings, ie; A:D
ColumnWidths
This determines the width of each Column used in a ListBox. The setting must be a String. and each width separated with the PC's List separator, usually the ; (Semicolon). See the help text below:
| Setting | Effect |
| 90;72;90 | The first column is 90 points (1.25 inch); the second column is 72 points (1 inch); the third column is 90 points. |
| 6 cm;0;6 cm | The first column is 6 centimetres; the second column is hidden; the third column is 6 centimetres. Because part of the third column is visible, a horizontal scroll bar appears. |
| 1.5 in;0;2.5 in | The first column is 1.5 inches, the second column is hidden, and the third column is 2.5 inches. |
| 2 in;;2 in | The first column is 2 inches, the second column is 1 inch (default), and the third column is 2 inches. Because only half of the third column is visible, a horizontal scroll bar appears. |
| (Blank) | All three columns are the same width (1.33 inches). |
This Property takes a range address or name as it's value. For example A1:D50 or MyRange (in the case of a named range) are valid entries.
This Property determines how your list will look. There are only two choices fmListStylePlain and fmListStyleOption The first is the default and has no real visual effect. The second will place small squares to the right of each Item in the ListBox which become checked when the user select the Item.
This Property is used to assist the user in looking for a particular Item in the list. It takes effect as the user starts to type.
| Constant | Value | Description |
|---|---|---|
| fmMatchEntryFirstLetter | 0 | Basic matching. The control searches for the next entry that starts with the character entered. Repeatedly typing the same letter cycles through all entries beginning with that letter. |
| FmMatchEntryComplete | 1 | Extended matching. As each character is typed, the control searches for an entry matching all characters entered (default). |
| FmMatchEntryNone | 2 | No matching. |
If for example your list had the entries Aardvark,Absolute,Acorn,Addict etc and you set the MatchEntry to fmMatchEntryFirstLetter and the user typed A or a they would see Aardvark, if they then typed Ad or ad they would see Addict.
This determines whether the user can select more than one Item in the ListBox.
| Constant | Value | Description |
|---|---|---|
| fmMultiSelectSingle | 0 | Only one item can be selected (default). |
| fmMultiSelectMulti | 1 | Pressing the SPACEBAR or clicking selects or deselects an item in the list. |
| fmMultiSelectExtended | 2 | Pressing SHIFT and clicking the mouse, or pressing SHIFT and one of the arrow keys, extends the selection from the previously selected item to the current item. Pressing CTRL and clicking the mouse selects or deselects an item. |
When the setting is fmMultiSelectMulti the user can select more than one item in the ListBox.
Sets and/or returns the Item that will appear in the top of the List. So if you had a list that contained 10 rows and set TopIndex to 5 the user would only be able to see the last 6 rows. The first row is 0 (zero).