Thread: Populating combobox 2 with items that match criteria from combobox 1

Hello,

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.


I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

Please advise

okay, this is what I mean,

looking at a list with two levels of classification, it will populate the 2nd combobox with only the items that match the criteria specified in the 1st combobox

eg. If I select Big Cats in the 1st combobox, the 2nd combobox will offer the choices of those rows that have "Big Cats" in the first column, thus offering choices like "lynx","lion" etc.

I am trying it this way as the real sheet as at least 200 items and I'll be darned if I'm gonna make that into a dependant list setup.

Try this code in the combobox1 change event

Code:

Private Sub ComboBox1_Change()
    Dim cmb1 As String
    Dim rng As Range
    Dim frng As Range
    
    With Application
        .ScreenUpdating = False
    ComboBox2.Clear
    cmb1 = ComboBox1.Value
    Set rng = Range("A6").CurrentRegion
    rng.AutoFilter Field:=1, Criteria1:=cmb1
    Set frng = Range(rng.Range("B2"), rng.Range("B1").End(xlDown)).SpecialCells(xlCellTypeVisible)
    
    For Each r In frng
        ComboBox2.AddItem r
    Next r
    
        ActiveSheet.ShowAllData
        
        .ScreenUpdating = True
    End With
End Sub

wow. Thank you. Exactly what I was looking for.

Question, could you explain the code a little bit,

especially the portion with references to B1 and B2


and what is r mean?

BigBas,

I've used your example as it will be easier on the user to add items rather then dealing with separated lists.

the problem is that:
1) for criteria that acutally do not have any items in the list, the macro hangs and excel crashes. E.g. if I select "dogs" and there is only "Big Cats" and "Bears" in the list, error occurs.

Can you help with some error handling code so that combobox2 says "none availible" if that error occurs?

2) can an evaluation portion be added so that:
if the 7th column of r = "availilble" then it gets added to combobox2,
else it doesn't get added?

If you could provide any insight on this, I would be most appreciative

Kuraitori:

There are going to be a few approaches to best resolving your problem; it will be based on which works best for you.

1. I personally think your best bet is to limit combobox selections to options in the list. This way, an end user cannot select an option that is not on the list, thereby resolving the problem. If you absolutely must allow that a user type in unavailable options, I have added an amended code. Instead of entering the code in the combobox_change() even, it will be entered in the combobox_lostFocus() event. The disadvantage to this is that the code does not fire until the entry in completed. In order to be complete, the user will have to click outside of the textbox.

Code:

Private Sub ComboBox1_LostFocus()
    Dim cmb1 As String
    Dim rng As Range
    Dim frng As Range
    
    With Application
        .ScreenUpdating = False
    ComboBox2.Clear
    cmb1 = ComboBox1.Value
    
    If Sheets("Sheet1").ComboBox1.MatchFound = False Then
        MsgBox "None Available"
        Exit Sub
    Else
        Set rng = Range("A6").CurrentRegion
        rng.AutoFilter Field:=1, Criteria1:=cmb1
        Set frng = Range(rng.Range("B2"), rng.Range("B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
    
        For Each r In frng
            ComboBox2.AddItem r
        Next r
    End If
    
        ActiveSheet.ShowAllData
        
        .ScreenUpdating = True
    End With

End Sub

2. I'm not sure I understand what you need. Can you please update your dummy data sample so I can see what you are looking for?