excel - 在 VBA (Excel) 中使用表单设计器将项目添加到列表框

Question

我在尝试以编程方式将项目添加到

Form.Designer.Controls.Add("Forms.ListBox.1")

我尝试过使用该方法

Form.Designer.Controls.Add("Forms.ComboBox.1").AddItem "Item"

虽然这不会返回调试错误，但该项目也不会出现在表单中。



这是完整的类模块，我使用 AddList() 方法
https://github.com/thorlindberg/thorlindberg/blob/main/programmatic-userform/UserInterface.bas
这是调用它来生成表单的宏
https://github.com/thorlindberg/thorlindberg/blob/main/programmatic-userform/Example.bas

Answer 1

正如我在上面的评论中所说，您不能使用 FormDesigner 添加项目以你尝试的方式。但是您可以在(相同的)代码中编写UserForm_Initialize事件，能够添加必要的项目:

Sub createUserFormListBoxWithItems()
    Dim frm As Object
    Set frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) '3

    With frm
        .Properties("Caption") = "MyTestForm"
        .Properties("Width") = 300
        .Properties("Height") = 200
        .Properties("Name") = "frmLBTest"
    End With
    
    With frm.Designer.Controls.Add("Forms.ListBox.1")
        .Name = "MyListB"
        .top = frm.Properties("Height") - 150
        .left = 10: .width = 100: .height = 50
    End With
    
    Dim i As Long, myItem As String
    frm.CodeModule.InsertLines 2, "Private Sub UserForm_Initialize()" '2 for the case of automatically inserting of Option Explicit

    myItem = "MyItem"
    For i = 3 To 5
        frm.CodeModule.InsertLines i, "    Me.Controls(""MyListB"").AddItem """ & myItem & i & """"
    Next i
    frm.CodeModule.InsertLines i, "End Sub"

    VBA.UserForms.Add(frm.Name).Show
End Sub

如果您尝试多次使用代码，VBA 不接受相同的 UserForm名称，即使您在运行代码之前将其删除。它作为使用名称保留在 Excel 中的某个位置。当我玩这样的表单创建时，我正在使用一个以前更改表单名称和 Remove 的函数。之后才从项目中提取它。例如，下一段代码将使用列表框 list属性以使用数组加载项目。它将有一行调用上述函数:

Sub createUserFormListBoxWithItems_2()
    Dim frm As Object
     
    removeForm "frmLBTest"
    Set frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) '3
 
    With frm
        .Properties("Caption") = "MyTestForm"
        .Properties("Width") = 300
        .Properties("Height") = 200
        .Properties("Name") = "frmLBTest"
    End With
    
    With frm.Designer.Controls.Add("Forms.ListBox.1")
        .Name = "MyListB"
        .top = frm.Properties("Height") - 150
        .left = 10: .width = 100: .height = 50
    End With
    
    With frm.CodeModule
        .InsertLines 2, "Private arrItems" '2 for the case of automatically inserting of Option Explicit
        .InsertLines 3, "Private Sub UserForm_Initialize()"
        .InsertLines 4, "    arrItems = Split(""Apple,Banana,Clementine"", "","")"
        .InsertLines 5, "    Me.Controls(""MyListB"").list = arrItems"
        .InsertLines 6, "End Sub"
    End With
    
    VBA.UserForms.Add(frm.Name).Show
End Sub

Sub removeForm(frmName As String)
 Dim i As Long, strName As String
 If Not formExists(frmName) Then Exit Sub

 strName = "TestName"
tryAgain:
 On Error Resume Next
  frm.Name = strName
  If err.Number = 75 Then 'a previously used name...
     err.Clear                    'clear the error
        strName = strName & i: i = i + 1       'increment the new string
        frm.Name = strName: GoTo tryAgain  'test the new name again
  End If
 On Error GoTo 0
 ThisWorkbook.VBProject.VBComponents.Remove frm
End Sub