Excel VBA Loop Through Controls in UserForm

VBA & macros · Updated November 22, 2026

To loop through all controls on a UserForm in Excel VBA, use a For Each loop over the UserForm.Controls collection, checking each control’s TypeName or ControlType to filter by kind. For example, For Each ctl In Me.Controls iterates every control on the form, letting you read or modify properties like Value or Enabled in one pass.

Why Loop Through UserForm Controls?

UserForms often contain many text boxes, combo boxes, checkboxes, and option buttons. Writing separate code for each control is tedious and error-prone. A loop lets you apply the same operation—clearing inputs, validating data, disabling controls, or collecting values—to every control at once. This is especially useful in data-entry forms where you want a “Clear All” button or a validation routine that checks all fields before saving.

Understanding the Controls Collection

Every UserForm has a built-in Controls collection that holds all controls placed on it, including frames and multi-page tabs. The collection is zero-based and supports both For Each and indexed access. When you loop, you get each control as a generic Control object. To work with specific properties, you need to identify the control type.

Basic Loop Structure

The simplest loop looks like this:

Dim ctl As Control
For Each ctl In Me.Controls
    ' Do something with ctl
Next ctl

This works for any control. However, not all controls have the same properties. For instance, a Label has a Caption but no Value. Attempting to access ctl.Value on a label will cause a runtime error. Therefore, you must filter by type.

Filtering by Control Type

Use TypeName(ctl) to get the control’s class name (e.g., “TextBox”, “ComboBox”, “CheckBox”). Alternatively, use the ControlType property, which returns a numeric constant. The most reliable approach is to check TypeName because it works across all VBA hosts.

Example: Clear all text boxes and combo boxes:

Dim ctl As Control
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    End If
Next ctl

For checkboxes and option buttons, you might set Value = False.

Handling Frames and Multi-Page Controls

Controls placed inside a Frame or on a MultiPage are not directly in Me.Controls—they are in the frame’s or page’s own Controls collection. To loop through all controls recursively, you need to descend into each container.

Example recursive subroutine:

Sub ClearAllControls(frm As Object)
    Dim ctl As Control
    For Each ctl In frm.Controls
        If TypeName(ctl) = "Frame" Then
            ClearAllControls ctl  ' recurse into frame
        ElseIf TypeName(ctl) = "MultiPage" Then
            Dim pg As Page
            For Each pg In ctl.Pages
                ClearAllControls pg
            Next pg
        Else
            ' handle simple control
            If TypeName(ctl) = "TextBox" Then ctl.Value = ""
        End If
    Next ctl
End Sub

Call it with ClearAllControls Me.

Common Problems and Solutions

  • Error 438 (Object doesn’t support this property): You tried to access a property that the control doesn’t have. Always filter by type first.
  • Controls inside frames not being processed: As noted, you must recurse into containers.
  • Looping through all controls but skipping some: Some controls like TabStrip have their own pages; use the same recursion logic.
  • Performance issues with many controls: Loops are fast, but if you’re updating properties that cause repaints, use Application.ScreenUpdating = False before the loop and set it back to True after.
  • Changing control names inside loop: Avoid renaming controls while iterating; it can cause unexpected behavior.

Comparing Different Loop Methods

Besides For Each, you can use an indexed loop: For i = 0 To Me.Controls.Count - 1. This is useful when you need to delete controls or when you want to process them in reverse order. However, For Each is generally safer and more readable. There’s no built-in “filter” method; you must manually check types.

Frequently Asked Questions

How do I loop through only text boxes in a UserForm?

Use TypeName(ctl) = "TextBox" inside the loop. For example, to set all text boxes to empty: If TypeName(ctl) = "TextBox" Then ctl.Value = "".

Can I loop through controls on a worksheet as well?

Yes, but the syntax differs. For worksheet controls (like ActiveX buttons), use Sheet1.OLEObjects or Sheet1.Controls (for form controls). The UserForm method is for UserForms only.

How do I get the value of a control in a loop?

After filtering by type, access the appropriate property. For TextBox and ComboBox, use .Value. For CheckBox and OptionButton, use .Value (True/False). For ListBox, use .ListIndex or .Value depending on the MultiSelect property.

Why does my loop miss controls inside a Frame?

Because they are not in the form’s top-level Controls collection. You need to iterate through the Frame’s Controls collection, as shown in the recursive example above.

Is there a way to loop through all controls without recursion?

No, because containers like Frames and MultiPages nest controls. You must either recurse or manually flatten the hierarchy. Some advanced techniques use a loop with a stack, but recursion is simplest.

Can I loop through controls in a UserForm from a standard module?

Yes, if you pass the UserForm object to a subroutine. For example: Sub ProcessForm(frm As UserForm) and then call ProcessForm Me from the form’s code. Inside, use For Each ctl In frm.Controls.

Safety and Best Practices

Always save a copy of your workbook before running a macro that modifies controls, because macros cannot be undone with Ctrl+Z. When looping, avoid modifying the collection (like adding or removing controls) during iteration, as it can cause runtime errors. If you need to delete controls, collect them in an array first, then delete after the loop. Also, be mindful of control names—use meaningful names to make your code easier to read.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.