VBA Controls in Excel — Loop the Controls Collection and Reference Controls by Name

TL;DR — Every control on a UserForm lives in one place: the form's Controls collection. Reach it as Me.Controls, loop it with For Each, and address any control by its name as a string — Me.Controls("txtName"). That collection is what keeps a twenty-field form short: one loop instead of twenty named lines. Filter to the controls you mean with TypeName or TypeOf, because the collection also holds labels, frames, and buttons.

Private Sub cmdClear_Click()
    Dim ctl As MSForms.Control
    ' Empty every text box on the form in one loop:
    For Each ctl In Me.Controls
        If TypeOf ctl Is MSForms.TextBox Then ctl.Value = ""
    Next ctl
End Sub

You reach one control by typing its name: txtName.Value, lstItems.Clear. That works when you know the control as you write the code. The Controls collection is for the case where you do not — you want every text box, or a control whose name you built at run time. This is the hub of the UserForm control family, and the three guides here complete it. The Controls collection is how you address controls in bulk; a TextBox always hands back a String, so a typed number is text until you convert it; and a Frame is the container that gives a set of option buttons its own group. Master the collection, the string, and the container, and the individual controls — ListBox, ComboBox, CheckBox — all read the same way: a control is an object you reach through the form and talk to through its properties.

What you'll learn

• What Me.Controls is, and why a form is a collection rather than a screen
• Looping every control with For Each and acting on the ones you mean
• Filtering by type with TypeName and TypeOf so you skip labels and frames
• Reaching a control by its name as a string, for forms built from data
• Adding controls to a form while it runs
• The judgment call: when a loop beats twenty named references

The mental model: a form is a collection of control objects

A UserForm looks like a screen, but to your code it is a collection. Me.Controls is that collection — every text box, label, button, frame, and list box you dropped on the form, held as objects you can count and iterate. Me.Controls.Count tells you how many; Me.Controls(0) is the first (the collection is zero-based). Me is the form the code runs in, so inside a UserForm's own module Me.Controls and Controls mean the same thing.

MsgBox "This form has " & Me.Controls.Count & " controls"

Once you see the form as a collection, the repetitive parts of form code disappear. You stop writing txt1, txt2, txt3 and start writing a loop.

Looping every control with For Each

For Each walks the collection. The control variable is typed MSForms.Control — the generic type every control shares:

Dim ctl As MSForms.Control
For Each ctl In Me.Controls
    Debug.Print ctl.Name
Next ctl

This is the pattern behind "clear the form", "collect every value", "disable everything while I work". But the collection holds all controls — including the ones you did not mean — so a bare loop that writes ctl.Value crashes on the first label, because a label has no Value. That is the next problem.

Filtering by type with TypeName and TypeOf

Not every control has the property you want. A Label has no .Value; a CommandButton has no .Text. Touch the wrong one and you get run-time error 438, Object doesn't support this property or method. So filter first. TypeName(ctl) returns the type as a string; TypeOf ctl Is tests it directly:

For Each ctl In Me.Controls
    If TypeOf ctl Is MSForms.TextBox Then
        ctl.Value = ""                 ' safe: only text boxes reach this line
    End If
Next ctl

Use TypeOf ... Is in an If — it is the idiomatic test. Reach for TypeName(ctl) when you want the type name in a message or a Select Case over several types. The rule: never call a property inside a For Each over Controls without first proving the control is a type that has it.

Reaching a control by its name

Here is where the collection pays off. You can index it by a name string, and that string can be built at run time:

Dim i As Long
For i = 1 To 5
    Me.Controls("txtDay" & i).Value = ""   ' txtDay1 ... txtDay5
Next i

A form with fields named txtDay1 through txtDay5 needs one loop, not five lines. This is how data-driven forms work: the names follow a convention, and the code builds them. The catch mirrors the Application.Run and CallByName trap in the reflection family — the name is a string, so the compiler cannot check it. A typo like "txtDya1" is not a red squiggle; it is a run-time error when the line runs. Build the name from a known pattern, and guard it with On Error if any part comes from data.

Adding controls to a form while it runs

The collection is not fixed. Controls.Add creates a control while the form is open, which is how you build a form whose size depends on the data — one row of fields per record:

Dim box As MSForms.TextBox
Set box = Me.Controls.Add("Forms.TextBox.1", "txtDynamic", True)
box.Top = 10: box.Left = 10: box.Width = 120

The first argument is the class of control, the second its name, the third whether it is visible. Dynamically added controls have no code-behind events unless you wire them up with a WithEvents class — a real technique, but one to reach for only when a fixed layout genuinely cannot do the job.

The judgment call: when a loop beats twenty named references

The Controls collection is powerful, and that makes it easy to overuse. The honest rule: if you have a handful of controls you know by name, txtName.Value is clearer than Me.Controls("txtName").Value, and the compiler checks it. The loop earns its place when the controls are many and uniform — twenty day fields, a grid of inputs, every text box on the form — or when the name is genuinely decided by data. A loop over three controls is showing off; a loop over twenty is the only sane way to write it. Choose the collection when it removes real repetition, not to prove you can.

How ExcelMaster helps

The failures here are quiet and late: a For Each that crashes on the one label you forgot, a control name built from data that no longer matches, a property called on the wrong type. All of them surface as the same error 438 or "not found", at run time, on a form that looked finished.

ExcelMaster lets you describe the form's behaviour — "clear every text box", "read all the inputs into a row", "build five day fields from a list" — and it writes the Controls loop with the type filter and the name guard already in place. You keep the form and the code, and you skip the run-time hunt.

Frequently asked questions

What is the Controls collection in VBA?

It is the collection of every control on a UserForm, reached as Me.Controls. Each item is a control object — a text box, label, button, frame, or list box. You can count them with .Count, loop them with For Each, and reach one by index or by its name as a string. It is the standard way to act on many controls at once instead of naming each.

How do I loop through all controls on a UserForm?

Declare a variable As MSForms.Control and use For Each ctl In Me.Controls. Inside the loop, test the type with TypeOf ctl Is MSForms.TextBox before touching a property, because the collection also holds labels and buttons that lack that property and would raise error 438.

How do I reference a control by a name stored in a string?

Index the collection with the string: Me.Controls("txtName"). The name can be built at run time, such as Me.Controls("txtDay" & i). Because the name is a string, a typo is not caught until the line runs — it raises a "not found" error — so build the name from a known pattern and guard it.

Why does my For Each loop over Controls raise error 438?

Because you called a property the current control does not have — most often .Value or .Text on a Label, Frame, or CommandButton. The Controls collection holds every type, so filter with TypeOf ctl Is MSForms.TextBox (or the type you mean) before reading or writing that property.

Can I add controls to a form while it is running?

Yes. Me.Controls.Add("Forms.TextBox.1", "txtNew", True) creates one at run time, which is how you build a form sized to the data. Set its Top, Left, and Width afterwards. Dynamically added controls have no event handlers unless you connect them with a WithEvents class.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-28.

Related guides: VBA TextBox · VBA Frame · VBA UserForm · VBA ListBox · VBA ComboBox · VBA CheckBox · VBA On Error