Userform:Filter Cascading Comboboxes initially triggered by multipage index

To put this into perspective, I have been creating userforms for 20 years and have made all possible mistakes in my time.

So on that basis:

You are heading down a dead end.

Your Data Tree is over complicated and inefficient.

Your method of populating the comboboxes is poor

Your method of repopulating the comboboxes is poor.


So.

On my sheet "Comboboxes" I have two lots of data separated by one or more blank lines.

The userform uses the first block to populate its comboboxes in sequence.

Row 1= Combobox1, Row 2= Combobox2 etc.


When a combobox is changed

the userform searches column A of my sheet "Comboboxes" for the new value, this code ignores blank lines
So it searches blocks one and two.

If a match is found the daughter combobox is populated
If a match is not found then the daughter combobox is cleared.

My code is simple by design:

Dim changeflag As Integer

Private Sub ComboBox1_Change()
Call CBChange(1, 2)
End Sub
Private Sub ComboBox3_Change()
Call CBChange(3, 4)
End Sub

Private Sub CBChange(MyNo As Integer, NextNo As Integer)
If changeflag = 1 Then Exit Sub
Sheets("Comboboxes").Select
On Error Resume Next
Range("A1:A200").Find(What:=Me.Controls("Combobox" & MyNo).Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
Cells(ActiveCell.Row, 1).Select
if InStr(Me.Controls("Combobox" & MyNo).Value, ActiveCell.Value) > 0 Then
Count = ActiveCell.Row
Entries = Range("A" & Count).End(xlToRight).Column
Me.Controls("Combobox" & NextNo).List = Application.Transpose(Range(Cells(Count, 2), Cells(Count, Entries)).Value)
Me.Controls("Combobox" & NextNo).ListIndex = 1
Else
Me.Controls("Combobox" & NextNo).Clear
End If
End Sub


Private Sub UserForm_Activate()
changeflag = 1
Sheets("Comboboxes").Select
MyRows = Range("A1").End(xlDown).Row
For Count = 1 To MyRows
Entries = Range("A" & Count).End(xlToRight).Column
Me.Controls("Combobox" & Count).List = Application.Transpose(Range(Cells(Count, 2), Cells(Count, Entries)).Value)
Me.Controls("Combobox" & Count).ListIndex = 1
Next
changeflag = 0
End Sub