Userform buttons / textboxes in array

[vodkasoda]

This may be a silly question, apologies if it is ...

I have a Userform with a heading line & a number of data lines.

Each data line contains a Command Button followed by a number of TextBoxes.

The Userform was created by creating the first line & then cutting & pasting the other lines below it.

The fields are currently named as follows ... as you probably know :

CommandButton1 ... TextBox1 ... TextBox2 ... TextBox3 ... TextBox4
CommandButton2 ... TextBox5 ... TextBox6 ... TextBox7 ... TextBox8
CommandButton3 ... TextBox9 ... TextBox10 ... TextBox11 ... TextBox12
CommandButton4 ... TextBox13 ... TextBox14 ... TextBox15 ... TextBox16

What I'd prefer, is that these lines were an array, i.e, they were named as follows :

CommandButton(1) ... TextBox1(1) ... TextBox2(1) ... TextBox3(1) ... TextBox4(1)
CommandButton(2) ... TextBox1(2) ... TextBox2(2) ... TextBox3(2) ... TextBox4(2)
CommandButton(3) ... TextBox1(3) ... TextBox2(3) ... TextBox3(3) ... TextBox4(3)
CommandButton(4) ... TextBox1(4) ... TextBox2(4) ... TextBox3(4) ... TextBox4(4)

Could somebody please explain, if this is possible, how to go about it ?!?

Thanks ...

 

[Kenneth Hobson]

Not sure what you mean by header and data lines.

This example shows how to get the names of the controls on UserForm1. You can parse the name and get just control types if needed. While it also shows how to put all the controls into a control array, it may be more handy to parse the name to get just the ones that you want in your control array.

Sub myControls()
  Dim c As Control
  Dim a() As Control
  Dim i As Long
  For Each c In UserForm1.Controls
    i = i + 1
    Debug.Print c.Name
    ReDim Preserve a(i)
    Set a(i) = c
    Debug.Print "a(" & i; ")", a(i).Name
  Next c
End Sub