为什么VBA以编程方式创建列表框.当用户表单被加载时，列表消失

Question

当在Excel 365 64位的UserForm中以编程方式创建和初始化ListBox时，使用可扩展性外接程序参考集，一切都像预期的那样开始;ListBox在UserForm的ListBox中可见。列表项显示在列表框下陷窗口和底部的滚动条中。
1。加载前的表单

然而，一旦使用VBA.UserForms.Add(myUserform.Name)加载UserForm, ListBox窗口变为空，滚动条消失。2. 表单加载后，VBE视图即使它在UserForm, ListBox中显示为空。列表中仍有条目

并且，当执行Show时，表单显示相同。3.显示的表单

下面是最小可复制的VBA代码示例:

Sub DisappearingListboxListMRE()
Dim myUserform As VBComponent
Dim lbxTest As MSForms.ListBox
Dim UF As Object

'Add a Userform
Set myUserform = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

'Create ListBox on the form
Set lbxTest = myUserform.Designer.Controls.Add("Forms.ListBox.1")
'Initialize Listbox
lbxTest.List = Split("Item 1;Item 2;Item 3", ";")

'After code stops here, open UserForm1 in the VBE. Note that the ListBox
'has three entries and scrollbars at the bottom. Then resume execution
Stop


'Load the form, but don't Show yet
Set UF = VBA.UserForms.Add(myUserform.Name)

'After code stops here, open UserForm1 in the VBE. Note that the ListBox
'has NO visible entries and LACKS scrollbars at the bottom. Then resume execution
Stop

'Note that the UF as displayed also lacks entries and scrollbars
UF.Show

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=myUserform
End Sub

有什么建议吗?对于ComboBox

也是如此

Answer 1

您需要在userform初始化事件处理程序中添加列表项…

Sub DisappearingListboxListMRE()
Dim myUserform As VBIDE.VBComponent
Dim lbxTest As MSForms.ListBox
Dim UF As Object
Dim strCode As String

'Create the userform
Set myUserform = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

'add a listbox
Set lbxTest = myUserform.Designer.Controls.Add("Forms.ListBox.1")

lbxTest.Name = "myListBox"

'Build the string for the Userform Initialize event handler
strCode = ""
strCode = "Private Sub UserForm_Initialize()"
strCode = strCode & vbCrLf & "    Me.Controls(""" & lbxTest.Name & """).List = Split(""Item 1;Item 2;Item 3"", "";"")"
strCode = strCode & vbCrLf & "End Sub"
'Add the Userform Initialize event handler
With myUserform.CodeModule
.InsertLines .CountOfDeclarationLines + 1, strCode
End With

'Load the form
Set UF = VBA.UserForms.Add(myUserform.Name)

UF.Show

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=myUserform

End Sub