Microsoft Office Tutorials and References
In Depth Information
Referencing UserForm Controls
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
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’ll then be able to take advantage of the Auto List Members feature,
which lets you choose the control names from a drop-down list.
Understanding the controls collection
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
 
Search JabSto ::




Custom Search