Loop Through Controls in a UserForm

How to loop through all controls in a UserForm; this allows you to do things like get values from the controls or to update them all at once.

Sections:

Code to Loop through Controls

Control Loop Examples

List of Control Names

Notes

Code to Loop through Controls

For Each FormControl In Me.Controls

    'Do something with the controls here.
    'FormControl is the object that will contain references to the control.

Next

This is the basic loop code that you can use in any UserForm in order to loop through all of the controls within it.

Note: this code should be placed within the form where you want to loop through the controls. If you want to loop through another form, you should replace Me.Controls with UserFormName.Controls where UserFormName is the name of the form that you want to loop through.

Control Loop Examples

Once you know how to loop through the controls, you probably want to do something with those controls.

There are two basic ways to do something with controls, using their name or the type of control. The name comes from the Name property and it is something that you can change for each control. The type of control is specific to each control type, TextBox, CommandButton, etc.

Loop through Controls Using their Name

This kind of loop isn't the most useful unless you are trying to get a list of the control names, but it's worth mentioning.

For Each FormControl In Me.Controls

    'Do something with the controls here.
    'FormControl is the object that will contain references to the control.

    'Output the name of the control
    MsgBox FormControl.Name


Next

When you put this into your form and run it, a message box will appear with the name of each control in the form.

Note that controls do not all have the same properties. For instance, though each control has the Name property, as used in the loop above, but each control does not have the Text property. As a result of this, you cannot do something like a blanket reset of the form using a loop without having to make exceptions for each type of control.

To reset all controls in a form, view our tutorial: Reset All Values in a UserForm

Loop through Controls Using their Type

This is more useful as it allows you to do things like go through all of the textbox controls in a UserForm and clear them or go through the optionbutton controls or checkbox controls.

For Each FormControl In Me.Controls

    'Do something with the controls here.
    'FormControl is the object that will contain references to the control.


    'Check only OptionButtons
    If TypeName(FormControl) = "OptionButton" Then

        'Do something with the OptionButton Controls.

    End If

Next

TypeName(FormControl) is what returns the type of control in the form.

In the next section, you will see a list of all of the control types so that you can easily reference them within the loop. For instance, OptionButton in the above loop is the control type for the option button form control.

List of Control Names

These names are found when you view the toolbox in the VBA window and hover over the controls for the form.

• Label
• TextBox
• ComboBox
• ListBox
• CheckBox
• OptionButton
• ToggleButton
• Frame
• CommandButton
• TabStrip
• MultiPage
• ScrollBar
• SpinButton
• Image
• RefEdit

Notes

Looping through the controls is the first part of the issue; next, you need to do something with the controls once you have located them. Here are some tutorials that will teach you how to access control properties and get data from as well as input data into form controls - all of this can be done within the above loops.

UserForm Control Properties Overview

Getting Data from a UserForm

Put Data into a UserForm

Make sure to download the sample file for this tutorial; all above code is contained in the form for the "Control Loop" button.