|
|
Replacing Data During UserForm Run-time
The ListBox or ComboBox list may be altered or replaced while the user has the UserForm open. The methods to be used depend on whether the data is bound or not. If the list was initialized by setting the List property with an array, or if the data is bound to a range, the operation may simply be repeated to change to the new list. If the list was created by AddItem and will be rebuilt the same way, the old data must be cleared out first using the Clear method, or else the new list will just be appended to the existing list. If the control has bound data, the Clear method will result in an error. To be safe, use the On Error Resume Next statement to prevent a possible Run-time error and On Error GoTo 0 to reset the error handler:
On Error Resume Next
ComboBox1.Clear
On Error GoTo 0
Initializing with a Range Address
A valid setting for the RowSource Property is a String. The String that we use would be either a valid cell address or a valid range name.
ComboBox1.RowSource= "A1:A10"
ComboBox1.RowSource="Sheet2!A1:D10"
ComboBox1.RowSource="MyRange"
All of the above are valid settings for the RowSource Property. If the UserForm will always be started and used with Sheet2 active, the first form is acceptable, but it is safer practice to always include the sheet name.
RowSource can be set at either Run-time or Design-time. If the RowSource were set at Design-time we would enter our String into the Property window of the ComboBox or ListBox without quotation marks, eg;
A1:A10
Sheet2!A1:D10
MyRange
The range specified can be either a single column or multiple columns.
Initializing with an Array
The two step to setting ComboBox or ListBox list with an array is to populate the array and then set the List property to it. The array must be a Variant array. The Dim statement defaults to defining a Variant variable, but sometimes it is better to state it explicitly as a reminder to future maintainers of the code.
There are lots of ways to fill an array. The Array function provides a one dimensional array, and setting an array equal to a range provides a two dimensional array. Neither requires a ReDim statement.
Before an array can be filled with a loop, it must be dimensioned. It can be dimensioned within the Dim statement, but it is much better to Dim without dimensions and use ReDim to provide the size. The ReDim statement can define either a one or two dimensional array.
Dim myArray 'or Dim myArray as Variant
Dim x As Integer, myRow As Integer, myCol As Integer
' no ReDim required
myArray = Array("red", "orange", "yellow", "green", "blue")
ComboBox1.List = myArray
' no ReDim required
myArray = Range("B10:E24")
ComboBox1.List = myArray
ReDim myArray(1 To 3)
For x = 1 To 3
myArray(x) = x
Next x
ComboBox1.List = myArray
ReDim myArray(1 To 5, 1 To 3)
For myCol = 1 To 3
For myRow = 1 To 5
myArray(myRow, myCol) = myRow * myCol
Next myRow
Next myCol
ComboBox1.List = myArray
Note that the rows of the control list correspond to rows of cells. If the data to be listed is spread across columns of the same row, then the range can be transposed as it is brought into the array, so that all the values will display.
myArray = WorksheetFunction.Transpose(Range("C13:F13"))
ListBox1.List = myArray
If a ComboBox or ListBox is bound because the RowSource property is set, then none of these methods will be legal, and an error will occur if tried.
Multicolumn ComboBoxes and ListBoxes
There are a number of properties that affect display and use of a multicolumn control.
ColumnCount
The ColumnCount Property is used to specify how many columns will be displayed in our ComboBox or ListBox. It is a number between 1 and 10 for unbound data, but may be more for bound data. A ColumnCount of 0 will display no columns, which might only be of use to hide all the contents! ColumnCount can be set at both Run-time and Design-time. A value of -1 will display all available columns. For example, if RowSource is “A1:C10”, a ColumnCount of -1 will display three columns.
ColumnWidth
The ColumnWidth property is used to set the width of each column. It is set as a string, both Run-time and Design-time. The default value is a blank, and the width of each column will be determined by Excel by dividing the width of the control equally among the columns, with a minimum width of 72 points (1 inch). 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, which is often a semicolon (;).
If the total column widths specified is greater than the width of the control, a horizontal scrollbar will appear.
Widths may be specified in cm or inches, or if no units are specified, the units defaults to points.
To specify another unit of measure, include the units as part of the values. The following examples from the Excel helps 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). |
ColumnHeads
This Boolean property defaults to False. When True, the columns have headings. If there is a vertical scrollbar, column headings stay at the top when the rest of the display is scrolled. The headings displayed are from the row above the RowSource range. For example, with headings in A1:D1, set
ListBox1.ColumnHeads=True
ListBox1.RowSource="A2:D10"
BoundColumn and TextColumn
Both ComboBox and ListBox have the BoundColumn property. This is the column of the selected multicolumn record that shows in the Value property. In addition, ComboBox has the TextColumn property which specifies which column shows up in the Text property and the text box part of the control.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.