Order of For Each Loop through controls in a frame?

[Aviashn]

Private Sub UserForm_Initialize()

Dim ctrl As Control
Dim x As Integer
x = 1

With UserForm1.Frame1
For Each ctrl In UserForm1.Frame1.Controls
ctrl.Value = x
x = x + 1
Next ctrl
End With

End Sub

 

[Aviashn]

Private Sub UserForm_Initialize()

My apologies, What determines the order? I thought it might be the
TabIndex, but that theory was wrong.

 

[Peter T]

The index order of controls, counting from zero, is the order they were
added to the form. The order cannot be changed, except of course if a
control is deleted subsequent control indexes are incremented down.

Regards,
Peter T

Private Sub UserForm_Initialize()

My apologies, What determines the order? I thought it might be the
TabIndex, but that theory was wrong.

 

[JLGWhiz]

I believe it is Left To Right And Down, like reading in English or using the
Tab key.

 

[JLGWhiz]

What order did you mean? Peter gave you the order that they are indexed. I
gave you the order that the macro looks for them.

 

[Aviashn]

Sorry I was not more specific. I was looking for the order in which
the For Each Loop would access the controls. I have a userform in
which I need to loop through controls and store the values in an
array, but need to know the specific order.

I believe Peter provided what I was looking for.

Thank you to both of you for your help!

 

[Dave Peterson]

If I had to make sure that the values are stored in the correct order, I'd
change the names nicely and then loop through those names:

If I had a bunch of comboboxes, I could use CBX_001, CBX_002, ..., CBX_999

Then use:

Dim iCtr as long
for ictr = 1 to 999
msgbox me.controls("CBX_" & format(ictr, "000")).value
next ictr

 

[JLGWhiz]

The method that Dave described is one way to be specific. You can also use
an If ... Then statement to be sure it is the correct control.

For Each ctrl In UserForm1.Controls
If ctrl.Type = msoControlComboBox Then
'Do something
End If
Next

So you can do what you want by exception rather than in a prescribed order.

 

[Dave Peterson]

If there are multiple comboboxes in that userform, then this may not loop in the
order that the user wants.

 

[Peter T]

That'd fail in a loop of userform controls which don't have a Type property.
msoControlComboBox is a 'type' constant of a commandbar control.

One way to check form controls -

If Typename(ctrl) = "ComboBox" Then ' case sensitive.

In passing, "For Each" loops in index/item order

Regards,
Peter T