What determines the order when you loop through controls?

[RawlinsCross]

Just a general question - say I have a userform with 6 option buttons and 3 checkboxes - when you use the code below, what determines the order vba uses to loop through the controls? Is it TabIndex? Is it the order they were added to the userform?

Dim ctrl as Control

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "OptionButton" Or TypeName(ctrl) = "CheckBox" Then
        Debug.Print ctrl.Name
    End If
Next ctrl

 

[Micron]

AFAIK, it is the index number of the control within the controls collection. Since this can change between today and tomorrow, it seems M$ decided that there was no need to access the index number that denotes where the control lies in the count of controls. So while you can know what the control name is for Me.Controls(2) whether that is done in a loop or directly, you can't know Me.Controls("myTextbox").Index = ? . I've yet to see a case where the index number is really required.

 

[Jaafar Tribak]

The order of the controls in the controls collection should be the order in which the controls were added to the userform.

you can't know Me.Controls("myTextbox").Index

Since looping through the controls follows the index order, you can use a loop until you get to the target control index.

You can use Me.Controls.Item(i) or even more descriptive Me.Controls.[_GetItemByIndex](i)

This little function takes a control object and returns its index in the zero based controls collection:

Function GetControlIndex(ByVal Ctrl As Control) As Long

    Dim i As Long
   
    For i = 0 To UserForm1.Controls.Count - 1
        If Ctrl Is UserForm1.Controls.[_GetItemByIndex](i) Then
            GetControlIndex = i
            Exit For
        End If
    Next

End Function

Usage :

Debug.Print GetControlIndex(myTextbox)

 

[Micron]

I think if you carefully interpret what I posted, I covered all of that. If a textbox was the 5th control to be added to the form, it's index might very well be (4) since, IIRC, the collection is zero based. So right away, the 1st control to be added is (0) which sort of negates the idea of the index of the first control added as being the first - unless you're savvy enough to consider that the first has an index number of zero. Furthermore, if the 5th control to be added (index = 4) is deleted, the sixth one to be added (index 5) now has the index of 4, yet it was the 6th one to be added, not the 5th. I imagine this could devolve into semantics, so I've said my piece and won't debate the issue further. After all, the original question, while a valid one, won't actually benefit from a concrete answer as it doesn't really matter what the order of iteration is over a controls collection.

 

[Jaafar Tribak]

I think if you carefully interpret what I posted, I covered all of that. If a textbox was the 5th control to be added to the form, it's index might very well be (4) since, IIRC, the collection is zero based. So right away, the 1st control to be added is (0) which sort of negates the idea of the index of the first control added as being the first - unless you're savvy enough to consider that the first has an index number of zero. Furthermore, if the 5th control to be added (index = 4) is deleted, the sixth one to be added (index 5) now has the index of 4, yet it was the 6th one to be added, not the 5th. I imagine this could devolve into semantics, so I've said my piece and won't debate the issue further. After all, the original question, while a valid one, won't actually benefit from a concrete answer as it doesn't really matter what the order of iteration is over a controls collection.

Fair enough. clearly, I ddin't carefully interpret what you posted and sure, there is no real practical benefit from knowing the index of a control other than the curiosity value.

Thanks.

 

[RawlinsCross]

Hi Guys, thanks for this discussion. I do have a case for knowing the order of the controls but as it turns out I added them in the exact order required. Jaafar, interesting syntax in your function, I never have used the [_function] syntax before - is there many examples of this?