Referencing User Form Controls

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.

Sub GetData()

UserForml.Show End Sub

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:

Sub GetData()

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:

MsgBox UserForml.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 UserForml:

Sub CountButtons()

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.



0 0


Post a comment