PEJK February 2016

How to control the interlinked comboBoxes output?

I have two ComboBoxes in a Userform.

The first Combox- ComboBox1 is updated with Items as soon as the Userform is initialized, as below.

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "A"
        .AddItem "B"
    End With
End Sub

Now once a value of the ComboxBox1 is selected, it updates the second ComboBox - ComboBox2, as below -

Private Sub ComboBox1_Change()    
    With ComboBox2
        .Clear
        .AddItem "P"
        .AddItem "Q"
        .AddItem "R"
    End With
End Sub

And the ComboBox2 on change displays its current value -

Private Sub ComboBox2_Change()
    MsgBox ComboBox2.Value
End Sub

Now when I select a value from ComboBox1 for the first time, ComboBox2 is updated and further on selecting a value from CombBox2, I get a popup message with the ComboBox2 value --- This is fine.

Now again I select a different value from ComboBox1, this time I get a blank popup message, (because it cleared the ComboBox2 content).

How can I handle this, as I don't want the MsgBox popup on changing the ComboBox1, I want the popup only on manually selecting values from ComboBox2?

Answers


Raystafarian February 2016

This is happening because once ComboBox2 has a value and you change ComboBox1 (and .clear combobox2 - it changes ComboBox2. Just use this for combobox2 -

Private Sub ComboBox2_Change()
If ComboBox2.ListCount = 0 Then Exit Sub
    MsgBox ComboBox2.Value
End Sub

Post Status

Asked in February 2016
Viewed 3,349 times
Voted 14
Answered 1 times

Search




Leave an answer