Looping thru controls on a form

[dave8]

I have a series of checkboxes on my form, chb1, chb2 ..... chb10. I use some vba code, "for each ctl in controls" that goes through each checkbox. Ideally, I want to traverse each control in the same order (chb1...chb10). For some reason, it loops from chb1, chb2, chb3, chb4, chb6, chb7...chb10, chb5. It skips chb5 until towards the end of the loop. Is there something in the checkbox properties where I can control the loop order?

 

[GTO]

Greetings Dave,

The For...Each seems to pick up on the order in which the controls were created. Even if this turned out to correctly rotate through the controls, I would suggest a For n = 1 to 10 to provide explicit instruction.

Changing the names appropriately, you can see the difference if you test like:

Option Explicit
  
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim n As Long
  
  For Each ctl In Me.Controls
    If TypeName(ctl) = "CheckBox" Then
      Debug.Print ctl.Name
    End If
  Next
  
  For n = 1 To 10
    Debug.Print Me.Controls("CheckBox" & n).Name
  Next
End Sub

Hope that helps,

Mark

 

[dave8]

Actually, the naming convention I use for the checkboxes are more complicated, ie., chkbx01x01, because I split the string and use them to do something else.
I was hoping to find a way to adjust the control so the loop will rotate directly to the control I want, if you understand what I mean.

 

[xenou]

If the actual names of your checkboxes are:

chkbx01x01
chkbx02x02
chkbx03x03
...
chkbx10x10

Then adjust GTO's example accordingly:

For n = 1 to 10
Debug.Print Me.Controls("chkbx" & Format(n,"00") & x & Format(n,"00")).Name
Next n

To control the loop order you cannot use a for each loop (as noted in the last post) since this offers no control over the order. You can use an array, which is what I usually do:

Dim arr
arr = Array("chkbx01x01","chkbx0505","chkbx03x03")
For i = 0 to ubound(arr)
    debug.print me.controls(arr(i)).Name
Next i

This example uses a "special order" rather than a sorted order of 1-10 - the earlier example is easier if the natural order is what you want.