Dynamically Create and Fill Value of Userform Textbox in VBA

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
102
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.

VBA Code:
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
How about
VBA Code:
    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
 
Upvote 0
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.?
 
Upvote 0
How are you running that code?
 
Upvote 0
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:
VBA Code:
Private Sub UserForm_Initialize()
    
    Call CharLoad

End Sub

Here again is the full CharLoad procedure:
VBA Code:
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:
VBA Code:
Sub RunForm()

    UserForm1.Show
    
End Sub
 
Upvote 0
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.

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

1593368159224.png
 
Upvote 0
Try
VBA Code:
newBox.Value = k & ".  " & dict.Keys(k - 1)
 
Upvote 0
I wonder if the issue might be that each of the created textbox's is given the same name parameter.
VBA Code:
Set newBox = UserForm1.mainFrm.Controls.Add("Forms.TextBox.1", "newBox")
I also notice that in post 5, the line
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top