2

I'm having issues trying to programmatically add items to a

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

I've tried using the method

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

And while this doesn't return a debug error, the item also doesn't appear in the form.


enter image description here


Here is the full class module, from which I use the method AddList()

https://github.com/thorlindberg/thorlindberg/blob/main/programmatic-userform/UserInterface.bas

And here is the macro that calls it to generate the Form

https://github.com/thorlindberg/thorlindberg/blob/main/programmatic-userform/Example.bas

3
  • 3
    You can't do that, as far as I know. There is nowhere to store list items at design time. Commented Mar 1, 2022 at 10:57
  • 1
    You cannot store list items at the design time in this way. But you can create the UserForm_Initialize event, where the items can be added. Practically the form will show with the loaded list box. I can show you how to proceed... Commented Mar 1, 2022 at 18:22
  • Your code works for me (when I change the function createForm (leave identifier = "Form1" out and turn around identifier = .Properties("Name") )) add the list later: in your Function RenderForm: With VBA.UserForms.Add(identifier) .Controls("Listbox").List = Array("Apple", "Banana", "Clementine") .Show End With Commented Mar 1, 2022 at 18:42

1 Answer 1

3

As I said in my above comment, you cannot add items using FormDesigner in the way you try. But you can write in (the same) code the UserForm_Initialize event, able to add the necessary items:

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

If you try playing with the code more than once, VBA does not accept the same UserForm name, even if you delete it before running the code. It remains somewhere in Excel as a used name. When I play with such forms creation I am using a function which previously change the form name and Remove it from the project only after that. For instance, the next piece of code will use the list box list property to load items using an array. It will have a line calling the above mentioned function:

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
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.