Excel VBA ComboBox2 doesn't get the right content

Question

I'm having a problem with the content of a combobox. On my userform, there are 3 comboboxes. Depending on the chosen item from combobox1, combobox2 should display either set 1 or set 2.

The same will be happening with the content of combobox 3, which depends upon the combination of chosen items from combobox 1 and 2.

However, I'm running into problems with combobox 2, which is always populated by set 2, even if I select the item in combobox 1 that should generate set 1 in the second combobox.

This is the code I used:

Private Sub UserForm_Initialize()
    With ComboBox1
        .Clear
        .AddItem "In contrast"
        .AddItem "Eng?"
        .AddItem "Trillers"
        .AddItem "Natuur(lijk)"
        .AddItem "Muziektrafiek"
    End With

    If ComboBox1.Value = "In contrast" Then
        GoTo LineComboBox1Set1
    End If

    If ComboBox1.Value = "Eng?" Then
        GoTo LineComboBox1set2
    End If

    If ComboBox1.Value = "Trillers" Then
        GoTo LineComboBox1set2
    End If

    If ComboBox1.Value = "Natuur(lijk)" Then
        GoTo LineComboBox1set2
    End If

    If ComboBox1.Value = "Muziektrafiek" Then
        GoTo LineComboBox1set2
    End If


LineComboBox1Set1:
     With ComboBox2
        .Clear
        .AddItem "Op verkenning"
        .AddItem "Gehoord? Gezien?"
        .AddItem "On stage"
        .AddItem "Creabende"
        .AddItem "Ingeblikt"
     End With

LineComboBox1set2:
     With ComboBox2
        .Clear
        .AddItem "Op verkenning"
        .AddItem "Gehoord? Gezien?"
        .AddItem "On stage"
        .AddItem "Creabende"
        .AddItem "Ingeblikt"
        .AddItem "Speak up"
        .AddItem "In de kijker"
    End With

Can anyone help me on this one?

Thanks a lot in advance!!

Kind regards, Marc

Kevin Ross Kevin Ross · Accepted Answer · 2010-04-19T10:27:15

It will do as your code does not end when you use the goto statement. I.e. you send the code to LineComboBox1set1 and the code executes that and then continues to run each line of code as there is nothing stoping it!

The quick fix is just to add "Exit Sub" after each "End With" but if it was my code I would refactor it by using a SELECT CASE switch i.e.

SELECT CASE ComboBox1.Value
     Case "In contrast"
           With ComboBox2
                Etc.....
           End With
     Case "Eng?", "Thriller" and so on
           With ComboBox2
                Stuff for set 2
           End With
  End Select

Excel VBA ComboBox2 doesn't get the right content

1 Answers