Loop through controls in a specific order

I have multiple controls of several types on a userform. How do I loop through them (using the For Each control In Me.Controls) in a specific order?
By default, the order is determined by the chronological order the controls were created. How do I change this?

TIA.

The simple answer is "you can't". Meaning a For Each loop will always traverse a collection from first to last item, and the order of the items in Controls cannot be manipulated.
However, it's of course quite possible to create your own array or collection of controls, sort that in whatever way you like, and then iterate through that structure.

If you'd like more concrete advice, you need to tell us more about what sort of "specific order" you mean. ;)

OK, so my structure is pretty simple:

1. Loop through Frames in UserForm by TabIndex
2. Loop through TextBox in Frame by TabIndex and then ComboBox in Frame by TabIndex

What kind of array should I create?

Two more questions, so that I can refine my suggestion:
- Does step 2 happen once or once per frame?
- Have you used classes before?

Yes, step 2 is for each frame.
I have used Class before for a simple control array.

Okay, then I'd approach it like this (note that this isn't quite working code, you'll have to account for special cases like the first item to be added to a collection, or an item added before the previous first item):


Create a class (let's call it ControlGroup) that groups one frame with the controls inside it, sorted by type, i.e. something like
Private myFrame As Frame
Private myTextBoxes As Collection
Private myComboBoxes As Collection

Provide public property pairs for the members.


Now, fill a master-collection with instances of this class, keyed and sorted by the frames' tab order:
Dim controlGroups As Collection
Dim thisGroup As ControlGroup, afterKey As String

Dim thisControl As Control
For Each thisControl In Me.Controls
If TypeOf thisControl Is Frame Then

For Each thisGroup In controlGroups
If thisGroup.pFrame.TabIndex < thisControl.TabIndex Then afterKey = thisGroup.pFrame.TabIndex
Next
Call controlGroups.Add(New ControlGroup, thisControl.TabIndex, After:=afterKey)
Set controlGroups(thisControl.TabIndex).pFrame = thisControl

End If
Next

Then, loop again over the Controls collection and add the controls to their ControlGroup:
For Each thisControl In Me.Controls
If TypeOf thisControl Is TextBox Then

For Each thisTextBox In controlGroups(thisControl.Parent.TabIndex).pTextBoxes
If thisTextBox.TabIndex < thisControl.TabIndex Then afterKey = thisTextBox.TabIndex
Next
Call controlGroups(thisControl.Parent.TabIndex).pTextBoxes.Add(thisControl, thisControl.TabIndex, After:=afterKey)

End If
Next


Whew. At that point, you have everything perfectly structured and sorted. All it takes now to do what you want are two nested For Each loops now, the outer over the ControlGroups/frames, the inner over the controls in that group/frame.

Hope that helps - it might look a little complex, but once you implement the class, writing the loop is actually pretty straightforward using intellisense. :)

Thank you very much. I'll check the code out.