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?!?...
Bookmarks