Loop through userform controls

[Magriza]

Hi everyone,

I've got a little code that cycles through controls on my multipage userform to write values to a worksheet. All the data is captured correctly but not in the correct order! This is the code:

    For Each mpPage In Me.mpQuestions.Pages
        For Each frmControl In mpPage.Controls
            If frmControl.Name Like "part*Ans*" Then
                With ws
                    .Cells(lrow, i).Value = Format(frmControl.Name, "#")
                    i = i + 1
                End With
            End If
        Next frmControl
    Next mpPage

What I'm trying to ascertain is how Excel/VBA decides what order to loop through the controls. I'm getting part1Ans1a and 1b followed by part1Ans2b, then 2a, and then (for some unknown reason) part 17. I'd really like to get part1Ans2a before 2b and part17 in between 16 and 18!

I've changed the TabIndex property accordingly but that hasn't helped. So I'm turning to anyone here to help! Please, this is driving me nuts!

p.s. The reason I'm using

If frmControl.Name Like "part*Ans*" Then

is because I'm collecting data from a mixture of TextBoxes, Labels and ComboBoxes, but also omiting data from the same!

 

[Andrew Poulsom]

I think the order is the order in which they were created. But you should be able to get the value of i from the control's name.

 

[Magriza]

Thanks Andrew - I was worried that'd be the answer! Now to find a way to loop using the tabindex value....