VBA 在用户表单中动态创建多个文本框/组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41945089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA create several textboxes/comboboxes dynamically in userform
提问by MCircular
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 generatedHere is my code. to simplify I allocated the row variable to Nb=3
我用组合框和文本框动态创建了一个用户表单。每行一个。用户将选择他想要的行数。到目前为止，我可以根据行数调整 Userform 的大小并创建第一行。但是第二行出现错误：运行时错误'-2147221005(800401f3)': Invalid Class String 结果是Userform生成这里是我的代码。为了简化我将行变量分配给 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
采纳答案by user3598756
The issue is in .Name = "Combobox" & i
问题在 .Name = "Combobox" & i
I suspect it's due to "Combobox1" being the defaultname of any newly inserted combobox control so that:
我怀疑这是由于“Combobox1”是任何新插入的组合框控件的默认名称，因此：
after first iteration you have a combobox younamed 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 explicitNameproperty assignment, defaults to "Combobox1" which, however, is already the name you assigned to the first combobox. hence the "Ambiguous Name" error
第一次迭代后，你有一个下拉框，你“Combobox1”而得名
在第二次迭代中，该
Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1")语句试图生成一个组合框，其名称在任何后续显式Name属性分配之前默认为“Combobox1”，但是，它已经是您分配给第一个组合框的名称。因此出现“歧义名称”错误
So there are three ways you can avoid the "Ambiguous Name" error:
因此，您可以通过三种方法避免“名称不明确”错误：
change
.Name = "Combobox" & ito.Name = "ComboBox" & iwhere the case difference is enough to avoid conflicting with the defaultname
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
Nameright at combobox instantiation
更改
.Name = "Combobox" & i为.Name = "ComboBox" & i其中大小写差异足以避免与默认名称冲突
完全省略那句话
并让 VBA 为您命名为“ComboBox1”、“ComboBox2”、...
用
Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1", Name:="Combobox" & i)即您
Name在组合框实例化时分配权限
Other than that, your code would hit the "userform_initialize" code writing issue since it would write as many subs as comboboxes to add
除此之外，您的代码会遇到“userform_initialize”代码编写问题，因为它会编写与要添加的组合框一样多的子程序
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
回答by MacroMarc
Set CBName = UF2.Designer.Controls.Add("forms.combobox." & i)
Set CBName = UF2.Designer.Controls.Add("forms.combobox." & i)
The class String is ALWAYS Forms.ComboBox.1 - never .2 or .3
类 String 总是 Forms.ComboBox.1 - 从不 .2 或 .3
Therefore do:
因此做：
Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1")
Set CBName = UF2.Designer.Controls.Add("Forms.ComboBox.1")
