Series of dependent comboboxes in userform that show unique values of dynamic tables

Give this a try.

If memory search me, I wrote that in Excel 2007.

Why didn't you check the solutions in this crossposted thread ?

http://www.ozgrid.com/forum/showthre...103#post707103

I've used the code and the final box doesn't seem to restrict the values? - it seems to provide details of ALL rows that equal the earlier combo box (this results in invalid combinations).. does anyone have any idea how to fix?? - or any advice?.. code is below:

Option Explicit
Dim i As Integer, a

Private Sub ComboBox1_Change()
    a = Sheets("JOBCODESEARCH").Range("JOBCODES").Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = (Me.ComboBox1) And Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_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
    With Me.ComboBox5
        .Enabled = False
        .Value = vbNullString
    End With
End Sub


Private Sub ComboBox2_Change()
    a = Sheets("JOBCODESEARCH").Range("JOBCODES").Value
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a, 1)
            If a(i, 2) = (Me.ComboBox2) And Not .Exists(a(i, 3)) Then .Add a(i, 3), a(i, 3) & "_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("JOBCODESEARCH").Range("JOBCODES").Value
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a, 1)
            If Me.ComboBox3 = vbNullString Then Exit Sub
            If a(i, 3) = (Me.ComboBox3) And Not .Exists(a(i, 4)) Then .Add a(i, 4), a(i, 4) & "_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()
    a = Sheets("JOBCODESEARCH").Range("JOBCODES").Value
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a, 1)
            If Me.ComboBox3 = vbNullString Then Exit Sub
            If a(i, 4) = (Me.ComboBox4) And Not .Exists(a(i, 5)) Then .Add a(i, 5), a(i, 5) & "_content"
        Next
        Me.ComboBox5.List = Application.Transpose(.Keys)
    End With
    With Me.ComboBox5
        .Enabled = True
        .Value = vbNullString
    End With
End Sub

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

In other words - I guess I'm trying to replicate filtering a spreadsheet... i.e. as you filter on each column from left to right - the only valid options for the 5th combobox should be based on the entries in the preceeding 4 comboboxes.... does anyone know how to achieve this?!?...

Untested, but I think in the If statement, you should include all previous combobox, so for example for Private Sub ComboBox2_Change(), it should be:

 If a(i, 1) = (Me.ComboBox1) And a(i, 2) = (Me.ComboBox2) And Not .Exists(a(i, 3)) Then .Add a(i, 3), a(i, 3) & "_content"

do the same logic for other combobox.

pangster,Akuini
Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Deleted........... didn't notice that this is such an old thread.