In my Excel 2010 and 2013 I've been 1

[Tomeczek]

In my Excel 2010 and 2013 I've been using a user form.
On the user form I have 20 text boxes defined as TexBox201, TextBox202, TextBox203, … , TextBox220.
I would like to access all of these text boxes in the loop rather than one by one.

Is there a way to change all these 20 text box individual names into an one array of names, like TextBoxes(i), where ‘i’ would be 1 to 20?

Thank you in advance for the advice.

 

[Andrzejek]

I don't think you can have a control array in VBA, but what you can do is:

Dim i As Integer

For i = 201 To 220
    Debug.Print Me.Controls("TextBox" & i).Name
    Debug.Print Me.Controls("TextBox" & i).Text
Next i

Have fun.

---- Andy

 

[Andrzejek]

ALso, if you want to go thru all text boxes (or any other controls) on your Form, you can do:

Dim cntr As Control

For Each cntr In Me.Controls
    If TypeOf cntr Is MSForms.TextBox Then
        Debug.Print cntr.Name
    End If
Next

Have fun.

---- Andy

 

[Tomeczek]

Thanks, Andrzejek, it works this way!
(Your first reponse).

 

[DaveInIowa]

Building on what Andy has provided...

Private tx As Integer
Private TextBoxes(201 To 220) As MSForms.TextBox

[green]' In the form initialize ...[/green]
For tx = LBound(TextBoxes) To UBound(TextBoxes)
    Set TextBoxes(tx) = Me.Controls("TextBox" & tx)
Next

[green]' Then you can use your text boxes more naturally ...[/green]
For tx = LBound(TextBoxes) To UBound(TextBoxes)
    Debug.Print TextBoxes(tx).Name
    Debug.Print TextBoxes(tx).Text
Next
    
TextBoxes(203).Text = "Tomeczek"

 

[Tomeczek]

Thanks Dave! It looks really good!