excel - VBA在用户窗体中动态创建多个文本框/组合框

Question

我动态创建一个带有组合框和文本框的用户表单。每行各一个。用户将选择他想要的行数。到目前为止，我可以根据行数调整用户窗体的大小并创建第一行。但第二行出现错误:运行时错误'-2147221005(800401f3)':无效的类字符串结果是Userform generated这是我的代码。为了简化，我将行变量分配给 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

Answer 1

问题出在 .Name = "Combobox"& i

我怀疑这是由于“Combobox1”是任何新插入的组合框控件的默认名称，因此:

• 第一次迭代后，您将获得一个以“Combobox1”命名的组合框您

• 在第二次迭代中，Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1") 语句尝试生成一个组合框，该组合框的名称位于任何后续操作之前显式 Name 属性分配，默认为“Combobox1”，但是，它已经是您分配给第一个组合框的名称。因此出现“名称不明确”错误

因此，可以通过三种方法来避免“名称不明确”错误:

• 将 .Name = "Combobox"& i 更改为 .Name = "ComboBox"& i

  大小写差异足以避免与默认名称发生冲突

• 完全省略该语句

  并让 VBA 为您命名“ComboBox1”、“ComboBox2”...

• 使用Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1", Name:="Combobox"& i)

  即您在组合框实例化时分配名称

除此之外，您的代码将遇到“userform_initialize”代码编写问题，因为它会编写与要添加的组合框一样多的子项

要面对上述所有问题并进行一些重构，您的代码可能如下:

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