Userform dependent Comboboxes

[Apple08]

Hi All

I would like to setup an userform having multiple dependent comboboxes.
Data is retrieved from the Sheets(“Summary”):
column A is duplicate value of portfolio names
column C is duplicate value of project names
column E to AA to show the budget data for the selectedproject.


I have tried the following code to filter portfolio andproject names, however only the portfolio name filter works, but not theproject name.


I just copied code into my project, so I hope someone cantell me what I have done wrong, and what is the best way to do after a projectname is selected from the combobox, then the budget data is shown foredit? Many thanks.

Private Sub UserForm_Initialize()
    Dim lr As Long, i As Long
    Dim dic As Object
    Dim arr As Variant
    
With Sheets("Summary")
    lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    'load array for speed
    arr = .Range("A3:A" & lr)
End With
Set dic = CreateObject("Scripting.Dictionary")
'use dictionary for uniques
For i = 1 To UBound(arr, 1)
  dic(arr(i, 1)) = 1
Next i
'populate combo
Me.ComboBox1.List = Application.Transpose(dic.keys)
End Sub
Private Sub ComboBox1_Change()
    Dim lr As Long, i As Integer
    Dim filtRng As Range, cel As Range
    
Application.ScreenUpdating = False
'remove existing list from combobox2
With Me.ComboBox2
    .Value = ""
    For i = .ListCount - 1 To 0 Step -1
        .RemoveItem i
    Next i
End With
'establish range to filter
With Sheets("Summary")
     lr = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
     Set filtRng = .Range("C3:C" & lr)
End With
'filter the range
filtRng.AutoFilter field:=1, Criteria1:=Me.ComboBox1.Value, VisibleDropDown:=False
'add items to combo list
For Each cel In filtRng.Offset(1, 2).SpecialCells(xlCellTypeVisible).Cells
    If cel.Value <> "" Then
        Me.ComboBox2.AddItem cel.Value
    End If
Next cel
'remove filter
filtRng.AutoFilter
Application.ScreenUpdating = True
End Sub

 

[Fluff]

You haven't given us much to go on, but this will populate the the two dependant comboboxes.

[COLOR=#ff0000]Dim ufDic As Object
[/COLOR]
Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox2.List = ufDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Change()
   
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   Dim i As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Summary")
   Set ufDic = CreateObject("scripting.dictionary")
   ufDic.CompareMode = 1
   Ary = Ws.Range("A3", Ws.Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value2
   For i = 1 To UBound(Ary)
      If Ary(i, 1) <> "" Then
         If Not ufDic.Exists(Ary(i, 1)) Then ufDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
         ufDic(Ary(i, 1))(Ary(i, 3)) = Empty
      End If
   Next i
   Me.ComboBox1.List = ufDic.Keys
End Sub

The line in red must go at the very top of the module, before any code

 

[Apple08]

Thanks Fluff! It works perfectly!

Please if I want to add more dependant comboxes, e.g. combobox 3 data in Column D depends on combobox 2, how should I edit the code? Many thanks.

 

[Fluff]

Like

Dim ufDic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox2.List = ufDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox3.List = ufDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   Dim i As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Pcode")
   Set ufDic = CreateObject("scripting.dictionary")
   ufDic.CompareMode = 1
   Ary = Ws.Range("A3", Ws.Range("A" & Rows.Count).End(xlUp).Offset(, 4)).Value2
   For i = 1 To UBound(Ary)
      If Ary(i, 1) <> "" Then
         If Not ufDic.Exists(Ary(i, 1)) Then ufDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
         If Not ufDic(Ary(i, 1)).Exists(Ary(i, 3)) Then ufDic(Ary(i, 1)).Add Ary(i, 3), CreateObject("scripting.dictionary")
         ufDic(Ary(i, 1))(Ary(i, 3))(Ary(i, 4)) = Empty
      End If
   Next i
   Me.ComboBox1.List = ufDic.Keys
End Sub

 

[Apple08]

You are brilliant, many thanks!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Apple08]

I am now able to get the combobox value, however how can I get the data in column E (textbox1) based on the combobox2 and 3 value please?

 

[Fluff]

How about

Dim ufDic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.TextBox1 = ""
   Me.ComboBox2.List = ufDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.TextBox1 = ""
   Me.ComboBox3.List = ufDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub ComboBox3_Change()
   Me.TextBox1 = ufDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   Dim i As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Pcode")
   Set ufDic = CreateObject("scripting.dictionary")
   ufDic.CompareMode = 1
   Ary = Ws.Range("A3", Ws.Range("A" & Rows.Count).End(xlUp).Offset(, 4)).Value2
   For i = 1 To UBound(Ary)
      If Ary(i, 1) <> "" Then
         If Not ufDic.Exists(Ary(i, 1)) Then ufDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
         If Not ufDic(Ary(i, 1)).Exists(Ary(i, 3)) Then ufDic(Ary(i, 1)).Add Ary(i, 3), CreateObject("scripting.dictionary")
         ufDic(Ary(i, 1))(Ary(i, 3))(Ary(i, 4)) = Ary(i, 5)
      End If
   Next i
   Me.ComboBox1.List = ufDic.Keys
End Sub

 

[Apple08]

Many thanks, however I have to match up quite a number of textboxes, due to my limited understanding of the code above, am I able to use something like if comboboxes value matches with Sheets(“Budget”) column A and D with unknown rows i then textbox1 value = (i, E)?

 

[Fluff]

I've absolutely no idea, as I don't understand what your asking.
Originally you were asking about a sheet called Summary, now you mention a sheet called Budget.
Do you want to fill various textboxes with the columns E to AA from the summary sheet?
Also once all three combos have been selected, will there only be one row that matches?