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

exceluserformvba

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.

enter image description here

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.

enter image description here

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

Any suggestions on how to do this, if possible? I'd hate to use the Excel spreadsheet itself to accomplish this.

Best Solution

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.

enter image description here

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
Related Question