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.



Was this article helpful?

+1 -3


  • marco
    How to count controls on a userform in excel 2007?
    1 year ago
    How to reference a module string in a form in vba?
    9 months ago
  • sago
    How to refer a userform in VBA?
    6 months ago
  • sonja luusua
    How to reference a useform vba?
    6 months ago
  • Saba Goytiom
    How to reference control name userform vba?
    5 months ago
  • aziz gebre
    How to refer form control buttons Excel VBA?
    5 months ago
  • cohen
    How to reference values from a userform from a module procedure?
    5 months ago
  • Gabriele
    How to reference a user control using vba?
    4 months ago
  • tyler harding
    How to reference form controls in vb in excel?
    3 months ago
  • Mara
    How to refer userform with variable?
    13 days ago
  • Uta Schultheiss
    How to reference command button in seperate user form?
    2 hours ago

Post a comment