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 UserForm1 .
Sub GetData() UserForm1.Show End Sub
Assume that UserForm1 contains a text box (named TextBox1 ), and you want to provide a default value for the text box. You could modify the procedure as follows :
Sub GetData() UserForm1.TextBox1.Value = "John Doe" UserForm1.Show End Sub
The controls on a UserForm make up a collection. For example, the following statement displays the number of controls on UserForm1 :
MsgBox UserForm1.Controls.Count
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 UserForm1 :
Sub CountButtons() Dim cbCount As Integer Dim ctl as Control cbCount = 0 For Each ctl In UserForm1.Controls If TypeName(ctl) = "CommandButton" Then _ cbCount = cbCount + 1 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. |