Multiple Cascading Comboxes in Userform

J

jbryce

New Member
Joined
May 18, 2011
Messages
3
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 (in combobox 1), 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.


<TABLE class=cms_table width=500><TBODY><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity</TD><TD class=cms_table_td>ID</TD><TD class=cms_table_td>Manager</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 4</TD><TD class=cms_table_td>1449628</TD><TD class=cms_table_td>Apple</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 4</TD><TD class=cms_table_td>1541836</TD><TD class=cms_table_td>Cash & Equivalent</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1597261</TD><TD class=cms_table_td>Cash & Equivalent</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1440081</TD><TD class=cms_table_td>Berkshire Hathaway</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1503456</TD><TD class=cms_table_td>Apple</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1600589</TD><TD class=cms_table_td>Cash & Equivalent</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1503002</TD><TD class=cms_table_td>Exxon</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1440102</TD><TD class=cms_table_td>Exxon</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1439284</TD><TD class=cms_table_td>Pandora</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1497420</TD><TD class=cms_table_td>SPDR S&P 500 ETF</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1647604</TD><TD class=cms_table_td>SPDR S&P 500 ETF</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1533584</TD><TD class=cms_table_td>Walmart</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 3</TD><TD class=cms_table_td>1533696</TD><TD class=cms_table_td>Walmart</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 2</TD><TD class=cms_table_td>1503009</TD><TD class=cms_table_td>Apple</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 2</TD><TD class=cms_table_td>1503003</TD><TD class=cms_table_td>Cash & Equivalent</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 2</TD><TD class=cms_table_td>1440083</TD><TD class=cms_table_td>Exxon</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 2</TD><TD class=cms_table_td>1502034</TD><TD class=cms_table_td>SPDR S&P 500 ETF</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 2</TD><TD class=cms_table_td>1449626</TD><TD class=cms_table_td>Berkshire Hathaway</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 1</TD><TD class=cms_table_td>1488053</TD><TD class=cms_table_td>Berkshire Hathaway</TD></TR><TR class=cms_table_tr vAlign=top><TD class=cms_table_td>Entity 1</TD><TD class=cms_table_td>1449605</TD><TD class=cms_table_td>Cash & Equivalent


</TD></TR></TBODY></TABLE>
Code: 
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
Dim y()
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 y()
        If .ListCount = 1 Then .ListIndex = 0
    End With
    
    QuickSort y()
    y() = Me.ComboBox2
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
My apologies, I had been working on the code and pasted it after some trial and error. Below is the correct code.


Code: 
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
Dim y()
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top