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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!