When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name. For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named userForml.
Assume that UserForml contains a text box (named TextBoxi), and you want to provide a default value for the text box. You could modify the procedure as follows:
UserForml.TextBoxi .Value = "John Doe" UserForml.Show End Sub
Understanding the Controls Collection
The controls on a UserForm make up a collection. For example, the following statement displays the number of controls on UserForml:
VBA does not maintain a collection of each control type. For example, there is no collection of CommandButton controls. However, you can determine the type of control by using the TypeName function. The following procedure uses a For Each structure to loop through the Controls collection and then displays the number of CommandButton controls on UserForml:
Dim cbCount As Integer Dim ctl as Control cbCount = 0
For Each ctl In UserForml.Controls
If TypeName(ctl) = "CommandButton" Then _ cbCount = cbCount + l Next ctl MsgBox cbCount End Sub
Another way to set the default value is to take advantage of the UserForm's Initialize event. You can write code in the UserForm_Initialize procedure, which is located in the code module for the UserForm. Here's an example:
Private Sub UserForm Initialize()
TextBox1.Value = "John Doe" End Sub
Notice that when the control is referenced in the code module for the UserForm, you don't need to qualify the references with the UserForm name. However, qualifying references to controls does have an advantage: You will then be able to take advantage of the Auto List Members feature, which lets you choose the control names from a drop-down list.
Caution Rather than use the actual name of the UserForm, it is preferable to use Me. Then, if you change the name of the UserForm, you won't need to replace the references in your code.
Was this article helpful?