How to create dynamic drop down lists using combo boxes? VBA EXCEL

Question

I am trying to create a 3 layer combo box drop down on one of my sheets (not in a form).

I achieved this using data validation and named ranges but the drop down arrow only appears when you click on the box which is annoying and I want this tool to be as intuitive as possible for the users.

I have found examples of 2 layer combo boxes using switch cases which I can kind of follow but the next layer is hurting my head.

Layer 1 - Route (30 Values)
Layer 2 - Conveyor Number (5-10 Values per Route)
Layer 3 - Record Reference (4-20 Values per Conveyor Number)

I want to read the values from the combo boxes and pass to various command buttons on the sheet.

If there is an easy way of doing this rather than writing out many switch cases that would be great

I have included an image of Route 1 to give context and an image of my dashboard.

I think I need to use an active x combo box to get it to work. I also want to the drop down to be located on sheet.

I have data I want to list available in a few different formats. I have in one big list (extract shown below)

I also have in named ranged (extract shown below)

Answer 1

OK, so I managed to find a way of creating a 3 layer, dynamic combo box drop down list. It may not be pretty and there will be some inefficiencies in my code but I'm still learning this stuff.

Heres my code:

To populate layer 1 drop down list as drop down is clicked for 1st time.

Private Sub ComboBox1_DropButtonClick()
    
    With ComboBox1
        .AddItem "R1"
        .AddItem "R2"
        .AddItem "R3"
        .AddItem "R4"
        .AddItem "R5"
        .AddItem "R6"
        .AddItem "R7"
        .AddItem "R8"
        .AddItem "R9"
        .AddItem "R10"
        .AddItem "R11"
        .AddItem "R12"
        .AddItem "R13"
        .AddItem "R14"
        .AddItem "R15"
        .AddItem "R16"
        .AddItem "R17"
        .AddItem "R18"
        .AddItem "R19"
        .AddItem "R20"
        .AddItem "R21"
        .AddItem "R22"
        .AddItem "R23"
        .AddItem "R24"
        .AddItem "R25"
        .AddItem "R26"
        .AddItem "R27"
        .AddItem "R28"
        .AddItem "R29"
        .AddItem "R30"
            
    End With
    
End Sub

Top populate layer 2 after layer 1 has been selected.

Private Sub ComboBox1_Change()
    
    Dim Route As Integer
    Dim Conveyor As Variant
    Dim RouteSelect As Variant
    Dim N As Integer
    
    ComboBox2 = ""
    ComboBox3 = ""
    Do While ComboBox2.ListCount > 0
        ComboBox2.RemoveItem (0)
    Loop
    
    Do While ComboBox3.ListCount > 0
        ComboBox3.RemoveItem (0)
    Loop
    
    ComboBox2.Clear
    ComboBox3.Clear
    
    Set tbl2 = ThisWorkbook.Worksheets("AllRoutes").ListObjects("AllRoutesTable")
    
    RouteSelect = ComboBox1.Value
    
    RouteSelect = Mid(RouteSelect, 2)
    
    
    For N = 1 To (tbl2.DataBodyRange.Rows.Count)
    
    Route = tbl2.DataBodyRange(N, 1).Value
    
    Conveyor = tbl2.DataBodyRange(N, 2).Value
    
    If RouteSelect = Route Then
    
    If Conveyor <> ConveyorLast Then
    With ComboBox2
            .AddItem Conveyor
    End With
    
    End If
    End If
    
    ConveyorLast = Conveyor
    
    Next
    
    
End Sub

To populate layer 3 after layer 2 has been selected.

Private Sub ComboBox2_Change()
    Dim Route As Integer
    Dim Conveyor As Variant
    Dim RecordReference As Variant
    Dim N As Integer
    
    ComboBox3 = ""
    
    Do While ComboBox3.ListCount > 0
        ComboBox3.RemoveItem (0)
    Loop
    
    Set tbl2 = ThisWorkbook.Worksheets("AllRoutes").ListObjects("AllRoutesTable")
    
    ConveyorSelect = ComboBox2.Value
    
    For N = 1 To (tbl2.DataBodyRange.Rows.Count)
    
    Conveyor = tbl2.DataBodyRange(N, 2).Value
    
    RecordReference = tbl2.DataBodyRange(N, 3).Value
    
    If ConveyorSelect = Conveyor Then
    
    
    With ComboBox3
            .AddItem RecordReference
    End With
    
    End If
    Next
    
    
End Sub

I have essentially made code that loops through the big list I have shown in my original question and looks for which rows contain the wanted information.

Hope this helps someone.

Any code improvement suggestions welcome