|
|
This 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.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.