[Excel VBA] How to Manipulate UserForm Controls by Name (String)

Introduction

In VBA UserForms, when changing a label’s text or a text box’s value, we typically write the control’s name directly, like Label1.Caption = "Text".

However, this method doesn’t work for dynamic processing where the control’s name isn’t known until the program runs. For example, if you want to “clear TextBox1 through TextBox10 in a loop,” you need a way to treat control names as strings.

In this article, I will explain a convenient technique to manipulate controls on a UserForm indirectly using their names (strings) via the Controls collection.

VBA Sample Code: Manipulating Controls by Name

This macro changes the Caption (display text) of a label control named StatusLabel located on a UserForm named DataForm.

This code works by specifying the form and the control name dynamically just before showing the form.

Standard Module Code

' Change properties by specifying the form control name as a string
Sub ShowFormAndSetControl()

    Dim formToShow As Object
    Dim targetControlName As String
    
    ' Set the target form and control name
    Set formToShow = DataForm
    targetControlName = "StatusLabel"
    
    ' --- Manipulate control by name using the Controls collection ---
    
    ' Check if the control exists to avoid errors
    On Error Resume Next
    Dim ctrl As Object
    Set ctrl = formToShow.Controls(targetControlName)
    On Error GoTo 0
    
    If Not ctrl Is Nothing Then
        ' Change properties of the control
        With formToShow.Controls(targetControlName)
            .Caption = "Ready"
            .ForeColor = RGB(0, 128, 0) ' Dark Green
        End With
    Else
        MsgBox "The specified control '" & targetControlName & "' was not found.", vbCritical
        Exit Sub
    End If

    ' Show the form
    formToShow.Show

End Sub

Preparation Before Execution

1. Create a UserForm named DataForm.
2. Place a Label control on it.
3. In the Properties window, change the (Name) of the label to StatusLabel.

Explanation of the Code

formToShow.Controls(targetControlName)

This is the core of this technique.

1. formToShow.Controls: Accesses the Controls collection of the DataForm. This collection contains all controls (Buttons, Labels, TextBoxes, etc.) placed on the form.
2. (targetControlName): Searches the Controls collection for a control whose Name property matches the string passed as an argument (in this case, "StatusLabel").

As a result, formToShow.Controls("StatusLabel") refers to the StatusLabel control itself. You can then chain properties like .Caption or .ForeColor to modify it, just as if you had written StatusLabel.Caption.

Note on Index Numbers

You can also specify controls by index number, like Controls(0). However, this index depends on the order in which controls were added to the form (different from TabIndex). Since this order can change easily when controls are added or deleted, it is unstable. Specifying by name is much more reliable.

Summary

In this article, I explained how to dynamically manipulate UserForm controls using their names (strings).

• You can access a specific control using the syntax Form.Controls("ControlName").
• This method allows you to handle multiple controls in a loop or switch target controls using variables.

Practical Application: Looping through TextBoxes

The most common use case for this is clearing multiple fields at once:

Dim i As Integer
For i = 1 To 10
    Me.Controls("TextBox" & i).Value = ""
Next i

Mastering this technique will significantly expand what you can implement with UserForms, allowing for more efficient and cleaner code.