Using loop function to set variable values for userform object

Question

I have 60 text boxes in my userform and I would like a quick way to set their values. I currently have:

  Dim TextB1_1, TextB1_2, TextB1_3, TextB1_4, TextB1_5, TextB1_6, TextB1_7, TextB1_8, TextB1_9, TextB1_10, TextB1_11, TextB1_12 As String
  Dim TextB2_1, TextB2_2, TextB2_3, TextB2_4, TextB2_5, TextB2_6, TextB2_7, TextB2_8, TextB2_9, TextB2_10, TextB2_11, TextB2_12 As String
  Dim TextB3_1, TextB3_2, TextB3_3, TextB3_4, TextB3_5, TextB3_6, TextB3_7, TextB3_8, TextB3_9, TextB3_10, TextB3_11, TextB3_12 As String
  Dim TextB4_1, TextB4_2, TextB4_3, TextB4_4, TextB4_5, TextB4_6, TextB4_7, TextB4_8, TextB4_9, TextB4_10, TextB4_11, TextB4_12 As String
  Dim TextB5_1, TextB5_2, TextB5_3, TextB5_4, TextB5_5, TextB5_6, TextB5_7, TextB5_8, TextB5_9, TextB5_10, TextB5_11, TextB5_12 As String

and to set the values I have:

TextB1_1 = Me.TextBox1_1.Text
TextB1_2 = Me.TextBox2_2.Text
TextB1_3 = Me.TextBox3_3.Text
TextB1_4 = Me.TextBox4_4.Text
TextB1_5 = Me.TextBox5_5.Text
TextB1_6 = Me.TextBox5_6.Text
TextB1_7 = Me.TextBox5_7.Text
TextB1_8 = Me.TextBox5_8.Text
TextB1_9 = Me.TextBox5_9.Text
TextB1_10 = Me.TextBox5_10.Text
TextB1_11 = Me.TextBox5_11.Text
TextB1_12 = Me.TextBox5_12.Text

I need to do the same for text box groups 2,3,4,5 and I though it would be easier to loop it and set the value to I for the group, however it does not work ie

  For i = 1 To 5
    TextB&i&_1 = Me.TextBox1_1.Text
    TextB&i&_2 = Me.TextBox2_2.Text
    TextB&i&_3 = Me.TextBox3_3.Text
    TextB&i&_4 = Me.TextBox4_4.Text
    TextB&i&_5 = Me.TextBox5_5.Text
    TextB&i&_6 = Me.TextBox5_6.Text
    TextB&i&_7 = Me.TextBox5_7.Text
    TextB&i&_8 = Me.TextBox5_8.Text
    TextB&i&_9 = Me.TextBox5_9.Text
    TextB&i&_10 = Me.TextBox5_10.Text
    TextB&i&_11 = Me.TextBox5_11.Text
    TextB&i&_12 = Me.TextBox5_12.Text
  Next I

is it possible to loop as such, and if so, what do I need to do as I cant get this to work. I have tried TextB1_&i too just in case that would simplify it, but that does not work either

Sixty text boxes sounds like a user’s nightmare, not giving meaningful names to variables sounds like a programmer’s nightmare. Whatever you are doing, it sounds like you need a collection of some Dictionary/Class/Map objects that would map the text boxes to the proper variables. Then loop through the collection. — JohnG
– JohnG, Commented Jun 13, 2018 at 3:45
12 months x 5 years = 60, add in approval from the users and its all good. the issue is around how to shorten it, not why. Thanks for the heads up on dictionary/class/map. ill look into it — Tejkaran Samra
– Tejkaran Samra, Commented Jun 13, 2018 at 4:07
I was simply trying to suggest that it appears a DataGridView may be easier to implement. 12 columns with 5 rows. — JohnG
– JohnG, Commented Jun 13, 2018 at 4:30

DisplayName · Accepted Answer · 2018-06-13 05:35:35Z

1

use an array to store values and Controls property of Userform object to address textboxes by their name:

Dim TextB(1 To 5, 1 To 12) As String
Dim i As Long, j As Long

With Me
    For i = 1 To 5
        For j = 1 To 12
            TextB(i, j) = .Controls("TextBox" & i & "_" & j).Text
        Next
    Next
End With

BTW, in VBA you have to explicitly declare all your String variables or they will be implicitly assumed as of Variant type i.e.:

Dim TextB1_1, TextB1_2, TextB1_3, TextB1_4, TextB1_5, TextB1_6, TextB1_7, TextB1_8, TextB1_9, TextB1_10, TextB1_11, TextB1_12 As String

will lead to TextB1_12 only being assumed as of String type, and all other ones as of Variant type.

while:

Dim TextB1_1 As String, TextB1_2 As String, TextB1_3 As String, TextB1_4 As String, TextB1_5 As String, TextB1_6 As String, TextB1_7 As String, TextB1_8 As String, TextB1_9 As String, TextB1_10 As String, TextB1_11 As String, TextB1_12 As String

will have all those variables being assumed as of String type

edited Jun 13, 2018 at 5:35

answered Jun 13, 2018 at 5:29

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

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

1 Comment

Tejkaran Samra Over a year ago

Quick note, this didn't work perectly for me, I had to make a tiny change so that it was: 'Year 1-5 Text boxes in an array For j = 1 To 5 For k = 1 To 12 TextB(j, k) = Controls("TextBox" & j & "_" & k) Next k Next j I didn't have the array With Me, it was throwing an error when I did. Many thanks plus appreciate the tips on string/variant type

Collectives™ on Stack Overflow

Using loop function to set variable values for userform object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related