VBA 在用户表单中动态创建多个文本框/组合框 [英] VBA create several textboxes/comboboxes dynamically in userform

问题描述

我动态创建了一个包含组合框和文本框的用户表单.每行一个.用户将选择他想要的行数.到目前为止，我可以根据行数调整 Userform 的大小并创建第一行.但是第二行出现错误:运行时错误-2147221005(800401f3)":无效的类字符串结果是用户表单生成这是我的代码.为了简化我将行变量分配给 Nb=3

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

推荐答案

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

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

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

• 在第一次迭代后，你有一个以Combobox1"命名的组合框you

在第二次迭代时，Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1") 语句试图生成一个组合框，其名称在任何后续显式 Name 属性分配，默认为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 到 .Name = "ComboBox" &我

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

where the case difference is enough to avoid conflicting with the default name

完全省略那句话

并让 VBA 为您命名为ComboBox1"、ComboBox2"、...

and have VBA name it for you "ComboBox1", "ComboBox2", ...

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

即您在组合框实例化时分配 Name 权限

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

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

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

这篇关于VBA 在用户表单中动态创建多个文本框/组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！