VBA create several textboxes/comboboxes dynamically in userform

Question

I create dynamically a Userform with comboboxes and textboxes. one of each per row. The user will choose how many lines he wants. So far I can adjust the size of the Userform according to the number of rows and create the first row. But an error occurred for the second row: Run-time error '-2147221005(800401f3)': Invalid Class String The result is Userform generated Here is my code. to simplify I allocated the row variable to Nb=3

    Public Sub CommandButton2_Click()

    Dim Nb As Integer 'Nb = number of people to record
    Dim UF2 As Object
    Dim TbHour, TbBin As msforms.TextBox 'txtbox for number of hours done and bins
    Dim CBName As msforms.ComboBox 'List with names
    Dim i 'i = loop to create rows

    Nb = 3

    Set UF2 = ThisWorkbook.VBProject.VBComponents.Add(3)
        With UF2
        .Properties("Caption") = "Packing record"
        .Properties("Width") = "250"
        .Properties("Height") = "50"
        .Properties("Height") = .Properties("Height") * Nb + 10
        End With

    For i = 1 To Nb
        Set CBName = UF2.Designer.Controls.Add("forms.combobox." & i) **'here is where the error happens on the second For/Next loop**
            With CBName
            .Name = "Combobox" & i
            .Top = 0
            .Top = .Top * i + 10
            .Left = 10
            .Width = 100
            .Height = 20
            End With

            With UF2.CodeModule
                .InsertLines 1, "Public sub userform_initialize()"
                .InsertLines 2, "Me.ComboBox1.AddItem (""1"")"
                .InsertLines 3, "End sub"
            End With

        Set TbHour = UF2.Designer.Controls.Add("forms.textbox." & i)
            With TbHour
            .Top = 0
            .Top = .Top * i + 10
            .Left = 120
            .Width = 50
            .Height = 20
            End With

    Next i

    i = i + 1
Set TbBin = UF2.Designer.Controls.Add("forms.textbox." & i)
        With TbBin
        .Top = 10
        .Top = .Top * i
        .Left = 180
        .Width = 50
        .Height = 20
        End With


    VBA.UserForms.Add(UF2.Name).Show
    ThisWorkbook.VBProject.VBComponents.Remove UF2


    End Sub

Why not just declare a New ComboBox variable, and .Add that object? — Mathieu Guindon
– Mathieu Guindon, Commented Jan 30, 2017 at 21:37
How do you write this? So far I always used 'controls.add("forms.xxxx.1")' — MCircular
– MCircular, Commented Jan 30, 2017 at 23:53
In simplified terms that fit a comment... Me.Controls.Add(New ComboBox) — Mathieu Guindon
– Mathieu Guindon, Commented Jan 31, 2017 at 0:19

user3598756 · Accepted Answer · 2017-01-31 10:33:40Z

The issue is in .Name = "Combobox" & i

I suspect it's due to "Combobox1" being the default name of any newly inserted combobox control so that:

after first iteration you have a combobox you named after "Combobox1"
at the 2nd iteration the Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1") statement is trying to generate a combobox whose name, before any subsequent explicit Name property assignment, defaults to "Combobox1" which, however, is already the name you assigned to the first combobox. hence the "Ambiguous Name" error

So there are three ways you can avoid the "Ambiguous Name" error:

change .Name = "Combobox" & i to .Name = "ComboBox" & i

where the case difference is enough to avoid conflicting with the default name
omit that statement altogether

and have VBA name it for you "ComboBox1", "ComboBox2", ...
use Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1", Name:="Combobox" & i)

i.e. you assign the Name right at combobox instantiation

Other than that, your code would hit the "userform_initialize" code writing issue since it would write as many subs as comboboxes to add

To face all what above issues and do some refactoring, your code could be as follows:

Option Explicit

Public Sub CommandButton2_Click()
    Dim nb As Integer 'Nb = number of people to record
    Dim UF2 As Object ' or use 'As VBComponent'
    Dim i 'i = loop to create rows

    nb = 3
    Set UF2 = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With UF2
        .Properties("Caption") = "Packing record"
        .Properties("Width") = "250"
        .Properties("Height") = "50"
        .Properties("Height") = .Properties("Height") * nb + 10

        .CodeModule.InsertLines 2, "Public sub userform_initialize()" '<--| start writing your "UserForm_Initialize" sub code
        For i = 1 To nb
            With .Designer.Controls.Add("Forms.ComboBox.1", Name:="Combobox" & i) ' or simply: With .Designer.Controls.Add("Forms.ComboBox.1")
                .top = 20 * (i - 1) + 5
                .Left = 10
                .Width = 100
                .Height = 20
            End With

            .CodeModule.InsertLines 2 + i, "Me.ComboBox" & i & ".AddItem (""1"")" '<--| keep adding lines to your "UserForm_Initialize" sub code

            With .Designer.Controls.Add("forms.textbox.1")
                .top = 0
                .top = 20 * (i - 1) + 5
                .Left = 120
                .Width = 50
                .Height = 20
            End With
        Next i
        .CodeModule.InsertLines 2 + i, "End sub" '<--| finish writing your "UserForm_Initialize" sub code

        i = i - 1
        With .Designer.Controls.Add("forms.textbox.1")
            .top = 20 * (i - 1) + 5
            .Left = 180
            .Width = 50
            .Height = 20
        End With


        VBA.UserForms.Add(.Name).Show
    End With
    ThisWorkbook.VBProject.VBComponents.Remove UF2
End Sub

Thank you! It works perfectly! I don't know why I tried something more complicated than needed to be

MacroMarc · Accepted Answer · 2017-01-31 00:41:17Z

1

Set CBName = UF2.Designer.Controls.Add("forms.combobox." & i)

The class String is ALWAYS Forms.ComboBox.1 - never .2 or .3

Therefore do:

Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1")

edited Jan 31, 2017 at 0:41

answered Jan 30, 2017 at 22:41

MacroMarc

3,3242 gold badges13 silver badges22 bronze badges

2 Comments

MCircular Over a year ago

That is what I thought but I have an error "Run-time: Ambiguous Name" on the same code line

MacroMarc Over a year ago

Yeh sorry I didnt spend a lot of time on this. user<numbers> gave you the idea to use the naming at creatin time: Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1", "Combo" & i)

Collectives™ on Stack Overflow

VBA create several textboxes/comboboxes dynamically in userform

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related