How to Add Item to ComboBox (populate ComboBox with items)
Users can select from predefined values assigned to the ComboBox. The UserForm creator has to be define this Values.
For example: gender (male/female), age (1, 2, 3, 4,…), etc.
Populate ComboBox when the value of another ComboBox change
To populate a ComboBox in accordance with another ComboBox value you need to use the CHANGE event.
As the value of first ComboBox changes you need to use the SELECT CASE function. In the example we will use 2 cases, but you can use more. Another possibility is to use the IF function, but whit this is harder to keep track especially when you have many possibilities and combinations. We advise on using the SELECT CASE.
Select Case
Private Sub ComboBox1_Change()
ComboBox2.Clear
'when CB1 value changes the list of CB2 will change
Select Case ComboBox1.Value
Case Is = "Value lower than 10" ' ' one of the values of combobox1. This is text and it doesn't matter the order of case, but when you have values it is important to use them in a logical order. EG: <10 is going to accept less values than <20 which also includes <10 therefore if you would put 20 first it will be all the time this selection.
With ComboBox2
.Clear 'clear list of CB2
.AddItem ("1 to 3")
.AddItem ("4 to 5")
.AddItem ("6 to 9")
'add as many items you need
End With
Case Is = "Value lower than 20"
With ComboBox2
.Clear
.AddItem ("11 to 13")
.AddItem ("14 to 15")
.AddItem ("16 to 19")
End With
End Select
End SubIf
Private Sub ComboBox1_Change()
ComboBox2.Value = ""
If ComboBox1.Value = "Value lower than 10" Then
' one of the values of combobox1. This is text and it doesn't matter the order of case, but when you have values it is important to use them in a logical order. EG: <10 is going to accept less values than <20 which also includes <10 therefore if you would put 20 first it will be all the time this selection.
With ComboBox2
.Clear
.AddItem ("1 to 3")
.AddItem ("4 to 6")
.AddItem ("7 to 9")
'add as many items you need
End With
Else
If ComboBox1.Value = "Value lower than 20" Then
With ComboBox2
.Clear
.AddItem ("10 to 13")
.AddItem ("14 to 16")
.AddItem ("17 to 19")
'add as many items you need
End With
End If
End If
End Sub
Leave a Reply
Want to join the discussion?Feel free to contribute!