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 =组合框&我
我怀疑这是由于 Combobox1是任何新插入的组合框控件的 default 名称,因此:
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:
-
更改
。Name= Combobox& i
到。Name= ComboBox& i
其中的大小写差异足以避免与默认名称
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", ...
使用设置CBName = UF2.Designer.Controls.Add( Forms.ComboBox.1,名称:= Combobox& i)
ie您可以在组合框实例化时为 Name
分配权限
i.e. you assign the Name
right at combobox instantiation
那样,您的代码将遇到 userform_initialize代码编写问题,因为它将编写与组合框一样多的子项以添加
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屋!