Reputation: 670
In Excel 2010, I am trying to create a userform dynamically in my vba code. However, after running the code, the list does not show when opening the dropdown. If I put a breakpoint before ".show", I can inspect the form in the design window and I see that the list is populated. Does the .show method clear the list or what is happening? The solutions I have found focus on populating from cell ranges or placing the ".additem"s in the initialization code of the userform. I don't want to do anything that requires me to create a second file. Everything should be in this vba code if possible. Any help appreciated. My code follows.
Sub make_combobox_form()
' Makes a form with only a simple combobox
Dim myForm As Object
Dim cb As MSForms.ComboBox
'Create the User Form
Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
With myForm
.Properties("Width") = 400
.Properties("Height") = 300
End With
Set cb = myForm.Designer.Controls.Add("Forms.ComboBox.1")
With cb
.Top = 100
.Left = 100
.Height = 20
.Width = 200
.AddItem "Item_1"
.AddItem "Item_2"
.AddItem "Item_3"
.value = "Item_1"
End With
VBA.UserForms.Add(myForm.name).Show
ThisWorkbook.VBProject.VBComponents.Remove myForm
End Sub
Upvotes: 2
Views: 1991
Reputation: 14053
Sub make_combobox_form()
'Create the User Form as component first
Dim myFormComponent As VBComponent
Set myFormComponent = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
' Get reference to user form from the UserForms collection then
Dim cb As MSForms.ComboBox
Dim myForm As Variant
Set myForm = VBA.UserForms.Add(myFormComponent.Name)
With myForm
.Width = 400
.Height = 300
End With
Set cb = myForm.Controls.Add("Forms.ComboBox.1")
With cb
.Top = 100
.Left = 100
.Height = 20
.Width = 200
.AddItem "Item_1"
.AddItem "Item_2"
.AddItem "Item_3"
.Value = "Item_1"
End With
myForm.Show
ThisWorkbook.VBProject.VBComponents.Remove myFormComponent
End Sub
Upvotes: 3