Multiple Cascading Comboxes in Userform

[Jbryce22]

Hi everyone. I'm trying to setup multiple cascading comboboxes in a userform. I've been able to get combobox2 to show the relevant managers for the selected entity in combobox1 (example: if I select Entity 4, it should display Apple and Cash & Equivalent in Combobox2). However, I would like for combobox3 to display the relevant ID when I select Apple in combobox2 (ID = 1449628). Right now, when I select Apple it shows both ID's for Entity 4. However, in the event that an entity has multiple holdings of a manager (Example: entity 3 has two managers for SPDR S&P 500 ETF), I would like for it to show both ID's in combobox3.

Any help is greatly appreciated.

Entity-ID-Manager
Entity 4 - 1449628 - Apple
Entity 4 - 1541836 - Cash & Equivalent
Entity 3 - 1597261 - Cash & Equivalent
Entity 3 - 1440081 - Berkshire Hathaway

Private Sub Userform_Initialize() 
    Dim x() As Variant 
    Set dic = CreateObject("Scripting.Dictionary") 
    With Sheets("Holdings For Export") 
        For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp)) 
            If Not IsEmpty(r) And Not dic.Exists(r.Value) Then 
                dic.Add r.Value, Nothing 
            End If 
        Next 
    End With 
    x() = dic.Keys 
    QuickSort x() 
    Me.ComboBox1.List = x() 
End Sub 
Private Sub ComboBox1_Change() 
    Dim b() As Variant 
     
    Me.ComboBox2.Clear: Me.ComboBox2.Clear 
    Set dic = CreateObject("Scripting.dictionary") 
    With Sheets("Holdings For Export") 
        For Each r In .Range("A2", .Range("C" & Rows.Count).End(xlUp)) 
            If r = Me.ComboBox1.Value Then 
                If Not dic.Exists(r.Offset(, 2).Value) Then 
                    Me.ComboBox2.AddItem r.Offset(, 2) 
                    dic.Add r.Offset(, 2).Value, Nothing 
                End If 
            End If 
        Next 
    End With 
    With Me.ComboBox2 
        If .ListCount = 1 Then .ListIndex = 0 
    End With 
     
End Sub 
Private Sub ComboBox2_Change() 
    Me.ComboBox3.Clear: Me.ComboBox3.Clear 
    Set dic = CreateObject("Scripting.dictionary") 
    With Sheets("Holdings For Export") 
        For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp)) 
            If r = Me.ComboBox1.Value Then 
                If Not dic.Exists(r.Offset(, 1).Value) Then 
                    Me.ComboBox3.AddItem r.Offset(, 1) 
                    dic.Add r.Offset(, 1).Value, Nothing 
                End If 
            End If 
        Next 
    End With 
    With Me.ComboBox3 
        If .ListCount = 1 Then .ListIndex = 0 
    End With 
End Sub 
 
 'http://home.pacbell.net/beban/
 'Copyright 2000 Alan Beban
Sub QuickSort(ByRef VA_array, Optional V_Low1, Optional V_high1) 
    On Error Resume Next 
     
     'Dimension variables
    Dim V_Low2, V_high2, V_loop As Integer 
    Dim V_val1, V_val2 As Variant 
     
     'If first time, get the size of the array to sort
    If IsMissing(V_Low1) Then 
        V_Low1 = LBound(VA_array, 1) 
    End If 
     
    If IsMissing(V_high1) Then 
        V_high1 = UBound(VA_array, 1) 
    End If 
     
     'Set new extremes to old extremes
    V_Low2 = V_Low1 
    V_high2 = V_high1 
     
     'Get value of array item in middle of new extremes
    V_val1 = VA_array((V_Low1 + V_high1) / 2) 
     
     'Loop for all the items in the array between the extremes
    While (V_Low2 <= V_high2) 
         
         'Find the first item that is greater than the mid-point item
        While (VA_array(V_Low2) < V_val1 And V_Low2 < V_high1) 
            V_Low2 = V_Low2 + 1 
        Wend 
         
         'Find the last item that is less than the mid-point item
        While (VA_array(V_high2) > V_val1 And V_high2 > V_Low1) 
            V_high2 = V_high2 - 1 
        Wend 
         
         'If the new 'greater' item comes before the new 'less' item, swap them
        If (V_Low2 <= V_high2) Then 
            V_val2 = VA_array(V_Low2) 
            VA_array(V_Low2) = VA_array(V_high2) 
            VA_array(V_high2) = V_val2 
             
             'Advance the pointers to the next item
            V_Low2 = V_Low2 + 1 
            V_high2 = V_high2 - 1 
        End If 
    Wend 
     
     'Iterate to sort the lower half of the extremes
    If (V_high2 > V_Low1) Then Call QuickSort(VA_array, V_Low1, V_high2) 
     
     'Iterate to sort the upper half of the extremes
    If (V_Low2 < V_high1) Then Call QuickSort(VA_array, V_Low2, V_high1) 
End Sub

[Bob Phillips]

Post the workbook, save us creating data.

[Jbryce22]

Here is the workbook.

Multiple Cascading Comboboxes.xlsm

[Jbryce22]

Bob, any thoughts on this?

[jaslake]

Hi Jbryce22
Try this code for your ComboBox2 Change Event

Private Sub ComboBox2_Change()
    Me.ComboBox3.Clear: Me.ComboBox3.Clear
    Set dic = CreateObject("Scripting.dictionary")
    With Sheets("Holdings For Export")
        For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If r = Me.ComboBox1.Value And r.Offset(0, 2).Value _
                    = Me.ComboBox2.Value Then
                Me.ComboBox3.Value = r.Offset(0, 1).Value
                Exit For
            End If
        Next
    End With
    With Me.ComboBox3
        If .ListCount = 1 Then .ListIndex = 0
    End With
End Sub

[Jbryce22]

Jaslake, that gets me really close. The only thing it is not doing is displaying ever associated ID with the defined security. For instance, in entity 3, SPDR S&P 500 ETF has two ID's (1497420 and 1647604). It is displaying the first ID, but not the second. I would hope that it would grab the second one and include it as well. I'm not sure if this is even possible?

Thanks a lot for all of the help. It is greatly appreciated.

[jaslake]

Hi Jbryce22
Sorry, didn't notice that...try this instead. Let me know of issues.

Private Sub ComboBox2_Change()
    Me.ComboBox3.Clear: Me.ComboBox3.Clear
    Set dic = CreateObject("Scripting.dictionary")
    With Sheets("Holdings For Export")
        For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If r = Me.ComboBox1.Value And r.Offset(0, 2).Value _
                    = Me.ComboBox2.Value Then
                Me.ComboBox3.AddItem r.Offset(0, 1).Value
            End If
        Next
    End With
    With Me.ComboBox3
        If .ListCount = 1 Then .ListIndex = 0
    End With
End Sub

[Jbryce22]

Jaslake, You are the man! Thanks tons!