What order does VBA look at userform controls?

  • Thread starter Thread starter Brad Patterson
  • Start date Start date
B

Brad Patterson

I have the following code ...

For Each CT In userform1
If TypeOf CT Is MSForms.TextBox Then
CT.Text = ThisWorkbook.Worksheets("sheet1").Range("A1")(2, i).Value
i = i + 1
Else: End If
Next CT

Now, the tab order of my form is textbox1,2,3,4,5,6,7,8,9. However, the vba
code produces the order textbox 1,2,6,7,3,4,5,8,9 ... This screws up my
attempt at populating the textboxes with the values from columns of a table
of data ...

How do I get it to go through them in the order I want?
 
What order does VBA look at userform controls?Brad,
I believe that if you add textboxes to the form in the order that you want
to tab thru and to load them, the tab order and order in the collection will
be the same, but this is shaky ground. The most reliable way to access a
specific item in a collection is thru its index. In the case of textboxes
the textbox name is its index in the collection. If the names of each of
your textboxes were to end in one of a series of integers in some
significant order, it would be easy to specify the textboxes in order in a
counter controlled loop.

For i = 1 to 10
UserForm.Controls("Textbox" & Cstr(n)).Text = <something>
Next i

Bob Kilmer

I have the following code ...

For Each CT In userform1
If TypeOf CT Is MSForms.TextBox Then
CT.Text = ThisWorkbook.Worksheets("sheet1").Range("A1")(2, i).Value
i = i + 1
Else: End If
Next CT

Now, the tab order of my form is textbox1,2,3,4,5,6,7,8,9. However, the vba
code produces the order textbox 1,2,6,7,3,4,5,8,9 ... This screws up my
attempt at populating the textboxes with the values from columns of a table
of data ...

How do I get it to go through them in the order I want?
 
What order does VBA look at userform controls?try something like

For i = 1 To 9
UserForm1.Controls("textbox" & i).Value = Range("a1").Offset(i, 0)
Next i

Lynn S
I have the following code ...

For Each CT In userform1
If TypeOf CT Is MSForms.TextBox Then
CT.Text = ThisWorkbook.Worksheets("sheet1").Range("A1")(2, i).Value
i = i + 1
Else: End If
Next CT

Now, the tab order of my form is textbox1,2,3,4,5,6,7,8,9. However, the vba code produces the order textbox 1,2,6,7,3,4,5,8,9 ... This screws up my attempt at populating the textboxes with the values from columns of a table of data ...

How do I get it to go through them in the order I want?
 
Typo!
Correction:

For i = 1 to 10
UserForm.Controls("Textbox" & Cstr(i)).Text = <something>
Next i
 
Back
Top