adding multiple labels and textboxes to an Excel userform during runtime using vba

Question

I'm creating an inventory management tool with Excel VBA. I've created code that gathers a list of names from a drop down box on Internet Explorer and puts them into an array.

What I need to do is something similar to vba create several textboxes comboboxes dynamically in userform, but I would be dynamically adding labels for the user names and textboxes for the number of FLNs each person would be receiving. These would then go into a predefined userform I've already created.

Per the code example above, I realize I can't use .Name = "Textbox" & i to rename the next label or textbox. i has to equal to an ever-changing list, so it can't be set in stone; hence why there has to be as many labels and textboxes as UBound(UserArray).

UPDATED

Private Sub CreateControl()
    Dim newTxt As msforms.Control, newLbl
    Dim i As Integer, TopAmt
    Dim UserArray As String

    TopAmt = 30

    For i = LBound(MyArray) + 1 To UBound(MyArray) - 1
        Set newLbl = MultipleOptionForm.Controls.Add("Forms.Label.1")
        With newLbl
            .Name = "Label" & i
            .Left = 10
            .Top = TopAmt
            .WordWrap = False
            .AutoSize = True
            .Visible = True
            .Caption = MyArray(i)
            Debug.Print .Name,
        End With

        Set newTxt = MultipleOptionForm.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxt
            .Left = 150
            .Top = TopAmt
            .Visible = True
            .Width = 20
            Debug.Print .Name
        End With
        TopAmt = TopAmt + newTxt.Height
    Next

    MultipleOptionForm.Show
End Sub

The approved answer in stackoverflow.com/questions/41945089/… explains it better than I can. From what I gathered, Combobox1 is a default name for the control, so I can't use "Combobox" & i to name it. They suggested that "ComboBox" & i would work. That's not the bulk of my question. My question is how to dynamically add labels and textboxes to a predefined form. I thought putting the code for one each in a for...next loop would work, but it leaves the form blank, for some reason. — Lou
– Lou, Commented Sep 6, 2017 at 15:19
You can rename the controls unless another control has it's name. — user6432984
– user6432984, Commented Sep 6, 2017 at 15:22
Instead of worrying about the controls names, it would probably be easier to create two class level collections and add the controls to the collection as they are created. — user6432984
– user6432984, Commented Sep 6, 2017 at 15:56
In that case all you need to do is not use the default "base" name of "Textbox" and just use (eg) "txtBox" Then there's no chance of names colliding. — Tim Williams
– Tim Williams, Commented Sep 6, 2017 at 16:04

score 5 · Accepted Answer · 2017-09-06 19:37:15Z

5

Lou the answer to the question is misleading. The question wants to provide a default name when adding the control by changing its ProgID ( bstrProgID is a string that references the class that is to be created).

You can rename the new controls provided that another control does not have the same name.

You can also pass the controls name as an argument to the Controls.Add method.

Your labels are not showing is that you never set the Label.Caption value.

Private Sub CreateControl()
    Dim newLbl As MSForms.Label
    Dim newTxt As MSForms.Control
    Dim i As Integer, TopAmt
    Dim UserArray As Variant

    TopAmt = 50
    UserArray = Array("Cat", "Dog", "Horse", "Gorrilla")

    For i = LBound(UserArray) To UBound(UserArray)
        Set newLbl = MultipleOptionForm.Controls.Add("Forms.Label.1")
        With newLbl
            .Name = "Label" & i
            .Left = 50
            .Top = TopAmt
            .Visible = True
            .Caption = UserArray(i)
            Debug.Print .Name,
        End With

        Set newTxt = MultipleOptionForm.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxt
            .Left = 100
            .Top = TopAmt
            .Visible = True
            Debug.Print .Name
        End With
        TopAmt = TopAmt + newTxt.Height
    Next
End Sub

Next Issue: how do you get the data from these dynamically created textboxes?

Dim newTxt As MSForms.Control
For i = LBound(UserArray) To UBound(UserArray)
    set newTxt  =  MultipleOptionForm.Controls("Textbox" & i)
    If UserArray(i) <> newTxt.Value then
        'Do something
    End if
Next

edited Sep 6, 2017 at 19:37

answered Sep 6, 2017 at 15:51

user6432984

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

6 Comments

Lou Over a year ago

PERFECT!! Had to make a few modifications as everything was mushed together in my userform, but it works our perfectly. Updating my original code to show the results. Thanks.

Lou Over a year ago

Next issue...how do you get the data from these dynamically created textboxes? I tried using MultipleOptionForm.Textbox1.Value, but it's not allowing me to view it in a MsgBox.

user6432984 Over a year ago

I appended the anser to your "Next Issue" to my answer.

Lou Over a year ago

I just tried that and I'm still not getting the MsgBox to show. I even checked the button name (AsgnFLNButton) and the procedure that handles the click (Sub AsgnFLNButton_Click()). Not sure what I'm doing wrong. I should be able to get the value of the textbox, but it's like the form pops up and prevents the button click event to occur.

Lou Over a year ago

I'm still at a loss. If I make a function to accept the button click within the bulk of my code, the form does nothing. If I attach the function to the actual button itself, then I get an out of range error because there's nothing feeding the function from the bulk of the code. I even tried UserArray = Module1.VERIFY_MULTIPLE.UserArray and still get an error. Any ideas, or do I have to ask a separate question?

|

Collectives™ on Stack Overflow

adding multiple labels and textboxes to an Excel userform during runtime using vba

1 Answer 1

Next Issue: how do you get the data from these dynamically created textboxes?

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Next Issue: how do you get the data from these dynamically created textboxes?

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related