loop through txtboxes in userform then fill array with the text

[James]

I have 42 textboxes in a form and I want to Loop throgh all of them
sequentially and load the values into 2 separate arrays
textboxes 1-24 goes into array1
textboxes 25-42 goes into array2
how do I do that using this setup? is there a better way?
The OLEObjects("textbox" & number) works for sheet controls, does it work
for forms?
the textboxes are set up sequentially (ie. 1, 2, 3, 4, 5 etc). I was
thinking about using a for each loop but couldnt figure out how to load the
seccond array (array2).
any help would be great, thanks

For j = 1 To 4
For i = 1 To 6
bxCount = bxCount + 1
'I GET AN ERROR HERE
Array1(i, j) = Me.OLEObjects("TextBox" & bxCount).Object.Value
Next i
Next j

For j = 1 To 3
For i = 1 To 6
bxCount = bxCount + 1
' 'I GET AN ERROR HERE
Array2(i, j) = Me.OLEObjects("TextBox" & bxCount).Object.Value
Next i
Next j

 

[Matthew Herbert]

I have 42 textboxes in a form and I want to Loop throgh all of them
sequentially and load the values into 2 separate arrays
textboxes 1-24 goes into array1
textboxes 25-42 goes into array2
how do I do that using this setup? is there a better way?
The OLEObjects("textbox" & number) works for sheet controls, does it work
for forms?
the textboxes are set up sequentially (ie. 1, 2, 3, 4, 5 etc). I was
thinking about using a for each loop but couldnt figure out how to load the
seccond array (array2).
any help would be great, thanks

For j = 1 To 4
    For i = 1 To 6
        bxCount = bxCount + 1
        'I GET AN ERROR HERE
        Array1(i, j) = Me.OLEObjects("TextBox" & bxCount).Object.Value
    Next i
Next j

For j = 1 To 3
    For i = 1 To 6
        bxCount = bxCount + 1
        ' 'I GET AN ERROR HERE
        Array2(i, j) = Me.OLEObjects("TextBox" & bxCount).Object.Value
    Next i
Next j

James,

The code below should get you pointed in the right direction.
Obviously, you'll have to modify it to fit your needs, but it
illustrates one way to loop through user form controls.

Best,

Matthew Herbert

Sub LoopUFTextBoxes()
Dim Ctrl As MSForms.Control

For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
Debug.Print Ctrl.Value
End If
Next Ctrl

End Sub