Transfer multiple textbox values to array

Question

I have a userform with 13 textboxes from which I'm trying to transfer the values of the last 10 (ie #4-#13) into an array. My thinking was to cycle through all controls on the form and use a simple counter to skip the first 3 textboxes, then set my array elements to the rest of the textbox values in turn.

For Each Ctrl In Me.Controls
    If TypeName(Ctrl) = "TextBox" Then
        i = i + 1
        If i > 3 Then
            PO(i - 3) = Ctrl.Value
        End If
    End If
Next Ctrl

The concept itself works; however, the cycle starts with textbox #3 instead of #1, and I end up getting the following values in my array - (#6-#13, #1, #2). So, the first textbox control it is finding is #3, then going through to #13 and coming back to #1 and #2.

The boxes are named (and tab indexed) in the logical order to fit what I'm trying to do, but obviously there is something about this procedure that I don't quite get - or VBA gets it in a different way than I do.

Is it possible that the For Each Ctrl statement goes through controls in the order in which they were originally created, rather than the order of the tabindex or name? If so, how could I find the original order of creation?

Are you using Option Base 1, or how did you ReDim your array? Because it looks like you're assigning your first array value to position 1. — dwirony
– dwirony, Commented Jul 6, 2018 at 17:00
They are named Textbox1 through Textbox13, and my array is previously dimmed 1 to 10 - hence, skipping the first 3 before starting to transfer values. I wish to skip Textbox1, 2 and 3. — CAW
– CAW, Commented Jul 6, 2018 at 17:07

SJR · Accepted Answer · 2018-07-06 17:10:15Z

3

You can do it like this, using the consistent structure of the controls' names.

For i = 4 To 13
    PO(i - 3) = Me.Controls("Textbox" & i).Value
Next i

answered Jul 6, 2018 at 17:10

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CAW Over a year ago

Perfect. Thank you. I did not know the Controls(Name) syntax. Would still like to know how the version I used actually searches through the control list.

SJR Over a year ago

My pleasure. I think it might be the order in which they are placed on the form. You could test it by setting up a new form and adding a few controls.

Collectives™ on Stack Overflow

Transfer multiple textbox values to array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related