3x dependent drop-downs

[JohnBi]

Good day to everyone.
I am not able to set up properly the 3x dependent drop-downs.:
Column B = first dropdown; then Column I for the second drop-dow and Column Q for the third
Only column B it is Ok. Then the 2nd and 3th dropdown not at all!
Could you please help to find out where am I wrong please?
Here is the code as result of researchs, copy and a little bit of luck ...!


Option Explicit
Private Sub ComboBox1_Change()
Dim sh As Worksheet
Dim r As Long
Dim m As Long
FillList
Me.ComboBox2.Clear
Set sh = Worksheets("New")
m = sh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For r = 2 To m
If sh.Range("B" & r) = Me.ComboBox1.Value Then
If Application.WorksheetFunction.CountIf(sh.Range("I2:I" & r), sh.Range("I" & r)) = 1 Then
Me.ComboBox2.AddItem sh.Range("I" & r).Value
End If
End If
Next r
End Sub

Private Sub ComboBox2_Change()
Dim sh As Worksheet
Dim r As Long
Dim m As Long
FillList
Me.ComboBox3.Clear
Set sh = Worksheets("New")
m = sh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For r = 2 To m
If sh.Range("I" & r) = Me.ComboBox2.Value Then
If Application.WorksheetFunction.CountIf(sh.Range("Q2:Q" & r), sh.Range("Q" & r)) = 1 Then
Me.ComboBox3.AddItem sh.Range("Q" & r).Value
End If
End If
Next r
End Sub

Private Sub ComboBox3_Change()
FillList
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim sh As Worksheet
Dim r As Long
Dim c As Long
Load UserForm1
Set sh = Sheets("New")
r = Me.ListBox1
For c = 1 To 10
UserForm1.Controls("TextBox" & c).Value = sh.Cells(r, c).Value
Next c
UserForm1.Show
FillList
End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim r As Long
Dim m As Long
Set sh = Sheets("New")
m = sh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For r = 2 To m
If Application.WorksheetFunction.CountIf(sh.Range("B2:B" & r), sh.Range("B" & r)) = 1 Then
Me.ComboBox1.AddItem sh.Range("B" & r).Value
End If
Next r
FillList
End Sub

Private Sub FillList()
Dim sh As Worksheet
Dim r As Long
Dim c As Long
Dim m As Long
Dim arr()
Dim n As Long
Dim f As Boolean
Set sh = Worksheets("New")
m = sh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For r = 2 To m
f = (sh.Range("B" & r).Value = Me.ComboBox1.Value) Or (Me.ComboBox1.ListIndex = -1)
If f Then
f = (sh.Range("I" & r).Value = Me.ComboBox2.Value) Or (Me.ComboBox2.ListIndex = -1)
If f Then
f = (sh.Range("Q" & r).Value = Me.ComboBox3.Value) Or (Me.ComboBox3.ListIndex = -1)
End If
End If
If f Then
n = n + 1
ReDim Preserve arr(1 To 19, 1 To n)
' Store the row number in the first (hidden) column
arr(1, n) = r
For c = 2 To 18
arr(c + 1, n) = sh.Cells(r, c).Value
Next c
End If
Next r

With ListBox1
'.ColumnHeads = True
.ColumnWidths = "20;20;100;50;0;0;0;0;0;60;0;0;0;50;0;00;50;50"
.ListStyle = fmListStylePlain
End With

If n > 0 Then
Me.ListBox1.Column = arr
Else
Me.ListBox1.Clear
End If
End Sub



Thanks in advance. John