 |
|
|
|
When
To Put Data In Our Control?
Now
we have decided which Control to use and where our data is going to come from,
we next need to decide when we are going to fill our Control. We can have this
done at Run-time or Design-time. If our data were being read from a Worksheet
range we would most likely do this at Design-time. However if we are going to
use the AddItem method we will be filling our Control at Run-time. If this is
the case you will probably find that most often you will want the Control filled
with the relevant data whenever the UserForm that is housing our Control is Initialized
by the user. When this is the case, all we need to do is drop our code that will
fill our ListBox or ComboBox into the UserForm_Initialize() Event.
There may be times when you want to alter the list or even replace it with new data while the user has the UserForm open. If you do need to replace the list that is in a ListBox or ComboBox you must clear the old data out first, else you will end up with two lists within the same Control. The way we can do this is by using the Clear Method. One thing to be aware of with this Method is that a Run-time error will be generated if you attempt to use the Clear Method on a ListBox or ComboBox that does not contain any data or is Bound* to a Data source**.
Describes a control whose contents are associated with a particular data source, such as a cell or cell range in a worksheet.
**Data
Source
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.
ComboBox1.Clear
On Error GoTo 0
Lets now look at the settings we can use to specify the columns in our Control.
ColumnCount - Applies to both a ComboBox and ListBox
The
ColumnCount Property is used to specify how many columns will be displayed in
our ComboBox or ListBox.
A
valid setting for the ColumnCount Property is a “Long” equal to or greater
than -1. We can set the ColumnCount at both Run-time and Design-time.
If
our Control has an *unbound
Data source then the ColumnCount has a 10 column limit, ie; 0-9
unbound
is described by the Excel help as:
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.
Once we have set our ColumnCount we may need to specify the width of each column. We do this by using the ColumnWidth Property.
ColumnWidth - Applies to both a ComboBox and ListBox
The
ColumnWidth Property is used to set the width of each column in a ListBox or
ComboBox that have a ColumnCount other than 0 (zero)
The ColumnWidth takes a String as it’s setting. It can be set at both Run-time and Design-time. If we leave this as it’s default (blank) or set it to “-1” the width of the column will be a calculated width as set by Excel. Using a setting of “0” will hide the column. Any other setting will specify the column width of the column it is applied to. To separate column widths use the list separator as set in the Regional Settings of the Windows control panel. This is often a semicolon (;) The text below is from the Excel help
Settings
To separate column entries, use semicolons (;) as list separators. In Windows, use the list separator specified in the Regional Settings section of the Windows Control Panel to change this value.
Any or all of the ColumnWidths property settings can be blank. You create a blank setting by typing a list separator without a preceding value.
If you specify a –1 in the property page, the displayed value in the property page is a blank.
To calculate column widths when ColumnWidths is blank or –1, the width of the control is divided equally among all columns of the list. If the sum of the specified column widths exceeds the width of the control, the list is left-aligned within the control and one or more of the rightmost columns are not displayed. Users can scroll the list using the horizontal scroll bar to display the rightmost columns.
The minimum calculated column width is 72 points (1 inch). To produce columns narrower than this, you must specify the width explicitly.
Unless specified otherwise, column widths are measured in points. To specify another unit of measure, include the units as part of the values. The following examples specify column widths in several units of measure and describe how the various settings would fit in a three-column list box that is 4 inches wide.
| 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 centimeters; the second column is hidden; the third column is 6 centimeters. 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). |
Remarks
In a combo box, the system displays the column designated by the TextColumn property in the text box portion of the control.
The other Property we will look at is the ColumnHeads Property.
ColumnHeads - Applies to both a ComboBox and ListBox
This
property allows us to have headings for the columns in a multi-column ComboBox
and ListBox.
A
valid setting for this is a Boolean (True or False). The default is False.
Let’s
assume we have headings on a Worksheet in the range A1:C1 and we have set the
ColumnHeads Property of a ListBox to True. To have our headings displayed in the
ListBox we would need to set the RowSource Property to:
ListBox1.RowSource="A2:C10"
In
other words we need to omit the first row when we fill our list then Excel will
use the row immediately above our RowSource as the headings.