Creating Multi-Dependent Combo-Boxes

  • Hi, I am trying to create a user form with multiple combo boxes that are dependent on each other. I have 4 combo boxes and each one is filtering so that the final combo box should have a single result


    So far, after a lot of research and help from past posts on this forum, I have managed to get the combo boxes to be dependent on the preceding combo box only. So when I get to the 4th combo box the selections for combo boxes 1 and 2 are ignored and only the selection on combo box 3 is taken into consideration.


    I would like to bind these all together so that the filtering on the last combo box is accurate


    This is the code I have so far and I also attached the file I'm working on. Additionally I would like to fill the bottom text boxes with the relevant data based on the criteria selected from the combo boxes when the Search button is clicked


    [VBA]Option Explicit
    Dim i As Integer, a


    Private Sub ComboBox1_Change()
    a = Sheets("sheet1").Range("table1").Value
    With CreateObject("scripting.dictionary")
    For i = 2 To UBound(a, 1)
    If a(i, 2) = Me.ComboBox1 And Not .Exists(a(i, 3)) Then .Add a(i, 3), a(i, 3) & "_content"
    Next
    Me.ComboBox2.List = Application.Transpose(.Keys)
    End With
    With Me.ComboBox2
    .Enabled = True
    .Value = vbNullString
    End With
    With Me.ComboBox3
    .Enabled = False
    .Value = vbNullString
    End With
    With Me.ComboBox4
    .Enabled = False
    .Value = vbNullString
    End With
    End Sub



    Private Sub ComboBox2_Change()
    a = Sheets("sheet1").Range("table1").Value
    With CreateObject("scripting.dictionary")
    For i = 1 To UBound(a, 1)
    If a(i, 3) = Me.ComboBox2 And Not .Exists(a(i, 4)) Then .Add a(i, 4), a(i, 4) & "_content"
    Next
    Me.ComboBox3.List = Application.Transpose(.Keys)
    End With
    With Me.ComboBox3
    .Enabled = True
    .Value = vbNullString
    End With
    End Sub



    Private Sub ComboBox3_Change()
    a = Sheets("sheet1").Range("table1").Value
    With CreateObject("scripting.dictionary")
    For i = 1 To UBound(a, 1)
    If Me.ComboBox3 = vbNullString Then Exit Sub
    If a(i, 4) = CDate(Me.ComboBox3) And Not .Exists(a(i, 1)) Then .Add a(i, 1), a(i, 1) & "_content"
    Next
    Me.ComboBox4.List = Application.Transpose(.Keys)
    End With
    With Me.ComboBox4
    .Enabled = True
    .Value = vbNullString
    End With
    End Sub


    Private Sub ComboBox4_Change()



    End Sub


    Private Sub UserForm_Initialize()
    a = Sheets("sheet1").Range("table1").Value
    With CreateObject("scripting.dictionary")
    For i = 2 To UBound(a, 1)
    If Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_content"
    Next
    Me.ComboBox1.List = Application.Transpose(.Keys)
    End With
    Me.ComboBox2.Enabled = False
    Me.ComboBox3.Enabled = False
    Me.ComboBox4.Enabled = False
    End Sub[/VBA]


    Any help would be very much appreciated

  • Try changing the code for changes to combo boxes 2 & 3 to

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Your second request is for something that bears no resemblance to the thread title, please ask that in a separate thread with an appropriate title.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks for the reply, currently getting an error type mismatch on the following line under the combo box 2 sub


    [VBA]
    With Me.ComboBox3.List = Application.Transpose(.Keys)
    [/VBA]


    I get that this is due to the date format of the field, is there any sort of work around for this?

  • Sorry, my bad. Delete the "With" at the start of that line.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.