Dynamically Create and Fill Value of Userform Textbox in VBA

[bemp87]

Hi Community, I am stuck in a VBA conundrum. I am trying to create multiple UserForm Textboxes in vba and Fill each one with a unique value either by index, or by some other means. I am able to get them to fill but when I do it seems that all the text boxes are being filled with the same values due to the there is no unique name distinction from textbox1 to textbox2, etc. I would imagine there has to be a way to do this, and I am stuck. Any help would be appreciated.

Sub CharLoad()

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim dict As New Scripting.Dictionary
    Dim charArray(1 To 4) As String
    Dim ColHeader As String
    Dim txtBox As MSForms.TextBox
    Dim h As Integer
   
    h = 0
    j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
   
    For i = 1 To j
        charArray(1) = Worksheets(1).Cells(i, 2)
        charArray(2) = Worksheets(1).Cells(i, 3)
        charArray(3) = Worksheets(1).Cells(i, 4)
        charArray(4) = Worksheets(1).Cells(i, 5)
        dict.Add Worksheets(1).Cells(i, 1), charArray
    Next i
    b = dict.Keys
    For k = 1 To j
        Set txtBox = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "txtBox")
        'tb = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "txtBox")
        txtBox.Top = h + 20
        txtBox.Left = 6
        h = h + 20
        'txtBox.BackColor = RGB(255, 255, 0)
            For i = 1 To j
            [COLOR=rgb(247, 218, 100)]UserForm1.mainFrm.txtBox.Value = i & ".  " & dict.Keys(i - 1)[/COLOR]
            'UserForm1.ListBox2.AddItem dict.Item(b(i - 1))(1)
            UserForm1.ListBox3.AddItem dict.Item(b(i - 1))(2)
            UserForm1.ListBox4.AddItem dict.Item(b(i - 1))(3)
            UserForm1.ListBox5.AddItem dict.Item(b(i - 1))(4)
            'Debug.Print dict.Item(b(i - 1))(k)
        Next i
    Next k
End Sub

The highlighted line of code in yellow is what keeps getting filled with the same value which is essentially creating 26 text boxes all having the same value which is the last value being retrived from the dictionary.

 

[Fluff]

How about

    For k = 1 To j
        Set txtBox = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "txtBox")
        'tb = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "txtBox")
        txtBox.Top = h + 20
        txtBox.Left = 6
        h = h + 20
        'txtBox.BackColor = RGB(255, 255, 0)
            UserForm1.mainFrm.txtBox.Value = k & ".  " & dict.Keys(k - 1)
            'UserForm1.ListBox2.AddItem dict.Item(b(k - 1))(1)
            UserForm1.ListBox3.AddItem dict.Item(b(k - 1))(2)
            UserForm1.ListBox4.AddItem dict.Item(b(k - 1))(3)
            UserForm1.ListBox5.AddItem dict.Item(b(k - 1))(4)
            'Debug.Print dict.Item(b(k - 1))(k)
    Next k

 

[bemp87]

Thanks for the response, i'm now seeing only the first txtBox is being filled with the last value from the loop. all resulting text boxes are blank. I'm starting to wonder if this is even possible. Not sure what else to try here, any other ideas on how this might can be accomplished.?

 

[Fluff]

How are you running that code?

 

[bemp87]

The code is being sun in a procedure titled "CharLoad" which is called by the Initialize Event for the UserForm. Full code as follows:

UserForm that calls the procedure CharLoad:

Private Sub UserForm_Initialize()
    
    Call CharLoad

End Sub

Here again is the full CharLoad procedure:

Sub CharLoad()

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim dict As New Scripting.Dictionary
    Dim charArray(1 To 4) As String
    Dim ColHeader As String
    Dim newBox As MSForms.TextBox
    Dim h As Integer
    
    h = 0
    j = WorksheetFunction.CountA(Sheets(1).Range("A:A"))
    
    For i = 1 To j
        charArray(1) = Worksheets(1).Cells(i, 2)
        charArray(2) = Worksheets(1).Cells(i, 3)
        charArray(3) = Worksheets(1).Cells(i, 4)
        charArray(4) = Worksheets(1).Cells(i, 5)
        dict.Add Worksheets(1).Cells(i, 1), charArray
    Next i
    b = dict.Keys
    For k = 1 To j
        Set newBox = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "newBox")
        'tb = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "txtBox")
        newBox.Top = h + 20
        newBox.Left = 6
        h = h + 20
        'txtBox.BackColor = RGB(255, 255, 0)
        'For i = 1 To j
        UserForm1.mainFrm.newBox.Value = k & ".  " & dict.Keys(k - 1)
        'UserForm1.ListBox2.AddItem dict.Item(b(i - 1))(1)
        UserForm1.ListBox3.AddItem dict.Item(b(k - 1))(2)
        UserForm1.ListBox4.AddItem dict.Item(b(k - 1))(3)
        UserForm1.ListBox5.AddItem dict.Item(b(k - 1))(4)
        'Debug.Print dict.Item(b(i - 1))(k)
        'Next i
    Next k
End Sub

Note: here is the code that activates the user form which is a simple macro assigned to a shape:

Sub RunForm()

    UserForm1.Show
    
End Sub

 

[bemp87]

Here is the result after executing the Macro: It creates the number of desired text boxes, but only fills the first one, based on the last value within the loop.

 

[Fluff]

It works for me, although I'm not quite sure why you are using a dictionary.

 

[Fluff]

Try

newBox.Value = k & ".  " & dict.Keys(k - 1)

 

[mikerickson]

I wonder if the issue might be that each of the created textbox's is given the same name parameter.

Set newBox = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "newBox")

I also notice that in post 5, the line

UserForm1.mainFrm.newBox.Value = k & ".  " & dict.Keys(k - 1)

refers to the .NewBox object in mainFrm, not the NewBox vba variable.
Fluff's code refers to the variable (that has a different value each loop).

In general, it might be a good idea to put all the created text boxes in a collection for later reference, unless they are the only controls in the frame, in which case the frame's Controls colletion would serve as that collection.

 

[bemp87]

Fluff / Mik- you guys are life savers. What annoys me the most is that I had this code originally and I figured the logic had to work out the way i wanted it to. Turned out i think i had a corrupt workbook. Having seen this work for you, I knew I wasn't insane in my thinking, so i copied the same code and information to a new workbook and just like that it worked like a charm.

@Mik- thanks for the suggestion. @Fluff- I use dictionaries for mostly everything regardless of the programming language, i guess it's just habit, but i'll look into using collections more often for vba as it seems it might be a better practice.