VBA Userform ComboBox instantiation

Question

I have a Problem with a Userform which I called "ComboTest2". It only consists of two Comboboxes. If I instantiate the USerform as an object then the following Code doesn't work in the sense that the second combobox of the Userform doesn't contain the desired data.

Sub FillCombo(ByVal row As Long)

    Dim rgCities As Range
    Set rgCities = Worksheets("Tabelle2").Range("B2:D2").Offset(row)
    ComboTest2.ComboBox2.Clear
    ComboTest2.ComboBox2.List = WorksheetFunction.Transpose(rgCities)
    ComboTest2.ComboBox2.ListIndex = 0

End Sub

Sub FillMain()

    Dim ComboForm2 As ComboTest2
    Set ComboForm2 = New ComboTest2
    ComboForm2.Show

End Sub

UserForm-Code:

Private Sub CommandButton1_Click()
    Me.Hide

End Sub

Private Sub CommandButton2_Click()
    Me.Hide
End Sub

Private Sub ComboBox1_Change()
    FillCombo ComboBox1.ListIndex
End Sub

Private Sub UserForm_Initialize()

    ComboBox1.List = Worksheets("Tabelle2").Range("A2:A5").Value
    ComboBox1.ListIndex = 0
    FillCombo ComboBox1.ListIndex
End Sub

But if I use the "default instantiation" by VBA which means that I change the FillMain Sub to:

Sub FillMain2()

    Dim ComboForm2 As ComboTest2
    Set ComboForm2 = New ComboTest2
    'ComboForm2.Show
    ComboTest2.Show

End Sub

Then everything is fine. Why is that so?

Best regards

Rory Rory · Accepted Answer · 2017-12-14T14:13:49

It's because FillCombo is referring to the userform by name, and therefore to the default instance (you're actually creating a new instance of the form). If that code is not in the userform, and I'm not sure why you would have it outside the form, you should pass the combobox as an argument to it.

VBA Userform ComboBox instantiation

1 Answers