Initializing UserForm Contro
LESSON WORKBOOK:
UserForms Lesson 6 2007.xlsxThis lesson will cover ways to initialize UserForm controls.
Many controls are given values at Design-Time, never to change. Labels and CommandButtons are prime examples. For those controls which are initialized at Run-time, there are two times to put data into UserForm controls, before opening, and during the opening process.
Several controls have a ControlSource property which points to a single cell. For ComboBox and ListBox, ControlSource can point to a single column range, and the RowSource property points to a multicolumn range. Putting data in the ControlSource and RowSource ranges before the UserForm is opened results in it opening with those values.
For most controls, initializing during the opening process is done by setting the Value property, but ComboBox and ListBox require special handling. Both can be set by adding one item at a time using AddItem, but only for single column controls. Multicolumn controls can only be initialized from a range of cells or with an array.
Examples of some of the following are found in the UserForm initialization procedures in the UserForm Control Demos file. They can all be found as follows:
Choosing the Initializing Method
Option Explicit
Private Sub UserForm_Initialize()
End Sub
Two of the considerations whether to initialize before or during the opening of the UserForm are whether the process is to be hidden from the user and whether the settings and data need to carry over to the next instance of the UserForm or even to the next usage of the workbook. The user doesn't see UserForm_Initialize, and can see cell contents unless the developer hides the cells or the worksheet. Cell contents are saved with the workbook and so are retained for the future. A third consideration is whether the data is the same size each time. An array loaded from cells can be set to take all the data even if the number of rows changes each time. A fourth consideration is whether values are to be used in formulas in the workbook. No doubt all such instances will have controls linked to cells.
As far as ease of use, data is often maintained in a database, or are records extracted from a database, and so are already in cells. These are easy to put into ComboBoxes and ListBoxes with either the ControlSource/RowSource or with an array during UserForm_Initialize.
Not all UserForm properties need to be initialized the same way. Some can be set by cell values and others by UserForm_Initialize, depending on the above considerations.
With all these possibilities, putting the data into cells and linking them to the controls is by far the most commonly used method.
Bound Data
Where the RowSource property of a control points to a cell or range, that data is bound. This places some restrictions on what can be done with the control. For example, the Clear method fails with a bound ListBox. The control is not bound if the ControlSource property references a cell, since that is an output property and not a source of list data.
Hiding Data in Cells
There are several methods of hiding data. In descending order, the hiding can be done with worksheets, rows or columns, or cell fonts. The surest method is by hiding the worksheet. Again there are two choices, False and VeryHidden. Format>Sheet>Hide sets the Visible to False, and a knowledgeable user can make it visible. The worksheet can be hidden beyond the user interface with:
Sheet1.Visible=xlVeryHidden
Again, the knowledgeable user can make it visible, but only with knowledge of macros. xlVeryHidden can cause difficulties during development, as the developer either has to leave it visible until just before each release, or has to provide a back-door method to hide and unhide it. The UserForm Control Demos file has such a back-door macro, <Shift><Ctrl>U, which toggles the Lesson 6 sheet between visible and xlVeryHidden.
Hiding and unhiding of rows and columns is done by Format>Column/Row>Hide/Unhide or by code several different ways. Where worksheets are hidden with the Visible property, ranges are hidden with the Hidden property. Rows and Columns are special forms of the Range object, and a single cell reference can be used to control Hidden by using the EntireRow or EntireColumn properties. xlVeryHidden does hide Columns and Rows, but does not protect them from unhiding by the user.
With ActiveSheet
.Columns("C").Hidden = True
.Rows("3").Hidden = True
Range("C3").EntireColumn.Hidden = False
Range("C3").EntireRow.Hidden = False
End With
An example of this can be found in the UserForm Control Demos file on the Lesson 2 sheet, where columns J:K are hidden. Attempting to hide a single cell will result in an error, such as with:
Range("C3").Hidden = True
The UserForm Control Demos file has a keystroke macro, <Shift><Ctrl>V, which toggles a row and a column between visible and hidden.
The last and least secure method of hiding cell contents is to simply change the font color to the same as the background, which is typically white. An example of this can be found in the UserForm Control Demos file on the Lesson 2 sheet, cells F12:G15.
Initializing with AddItem
This method can only be set at Run-time. If the ListBox or ComboBox has only one column then AddItem will add an item to the ListBox or ComboBox list. If the ListBox or ComboBox has more than one column then AddItem will add a new row to the ListBox or ComboBox list
The syntax for the AddItem property is: [ item [, varIndex]] Both arguments are optional. The item is used to specify the item or the row to add. The number of the first item or row is always 0 (zero), the second is 1, and the third is 2 and so on . The varIndex is used to specify the position within the Control where the item is to be placed. If you had a ComboBox that contained a ten row list and you used the AddItem to add another item you could use
ComboBox1.AddItem "Horse", 5
This would add the text Horse to our ComboBox as the fifth item or row in the list. If we had omitted the varIndex from the AddItem then the text Horse would be placed as the last item or row in the list.
The AddItem is best suited if
ท You have only a short list of entries to add
ท You need to increment the items added. In which case you could place the AddItem Property within a Loop.
For most other cases the RowSource Property is better suited.
Filling Using AddItem Loops
While setting the RowSource Property to a range address is the most common and probably the easiest way to fill a ListBox or ComboBox, there are situations where the AddItem method is a better option. For example, to fill a ComboBox or ListBox with times that begin on the hour and cover the next 24 hour period at 15 minute intervals, formulas on a worksheet could use the NOW() function and increment it by the needed increment, ie;
A1 =INT(NOW()*24)/24
A2 =A1+1/24/4
A96 =A95+1/24/4
Rather than waste file space with these formulas, use a loop.
Private Sub UserForm_Initialize()
Dim dTime As Date
dTime = Int(Now * 24) / 24
Do Until dTime >= Now + 1
dTime = dTime + 1 / 24 / 4
ListBox1.AddItem (Format(dTime, "hh:mm"))
Loop
End Sub
This code will only run when the UserForm is initialized, and the loop will fill ListBox1 with times that are incremented at 15 minute intervals over the 24 hour period beginning on the hour before.
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.
Microsoft ฎ and Microsoft Excel ฎ are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.