My Loop is Reversing. Any Idea Why?

[afrazier1221]

I have this:

Dim contr As Control
C = 1
For Each contr In UserForm1.Controls
If Left(contr.Name, 4) = "CDTB" Then
contr.Value = ""
contr.Value = Sheet5.Cells(C, 41).Value
C = C + 1
End If
Next

On the UserForm in question, there are eighteen textboxes, named CDTB1 through CDTB18 (Customer Data Text Box). The loop is meant to populate the form fields from Sheet5, Cells AO1 through AO18 (Rows 1 through 18, Column 41).

The code result reverses itself at 10 and produces essentially a mirrored result.

What I should get:

What I actually get:

I'm not sure why it's doing this. Any thoughts?

 

[RoryA]

The for each loop goes in order of control creation. Given that they are numbered, you should use that:

Dim contr As Control
For Each contr In UserForm1.Controls
If Left(contr.Name, 4) = "CDTB" Then
C = Clng(Mid$(contr.name, 5))
contr.Value =Sheet5.Cells(C, 41).Value
End If
Next

 

[CephasOz]

You're assuming that the order of a control in the controls is numerical. Try this, considering that you are only interested in the CDTB controls.

    Dim intCntr As Integer
    With Userform1
        For intCntr = 1 To 18
            .Controls("CDTB" & intCntr).Value = Sheet5.Cells(intCntr, 41).Value
        Next
    End With

 

[afrazier1221]

You're assuming that the order of a control in the controls is numerical. Try this, considering that you are only interested in the CDTB controls.

    Dim intCntr As Integer
    With Userform1
        For intCntr = 1 To 18
            .Controls("CDTB" & intCntr).Value = Sheet5.Cells(intCntr, 41).Value
        Next
    End With

This worked, thank you.

I would still like to understand how to identify the problem, though. I went through the Properties list, but couldn't find anything to identify the controls' order of creation, or their numerical order, outside of the numeric sequence I gave them myself with the assigned names. Is there a place where I can see this information, or to perhaps alter it to put the controls in numeric sequence?

 

[RoryA]

Is there a place where I can see this information, or to perhaps alter it to put the controls in numeric sequence

No, but as mentioned, you don't need to.