How to list combo box data based on another combo box entry

Question

I have 3 combo boxes, the first one contains Subjects, 2nd combo box has levels like beginner-advanced, then 3rd combo box has Trainer Names. The 2nd and 3rd combo boxes list should vary depending on the entry selected in the first combo box. I have an "Info" worksheet that has 2 tables where i should reference the list, both tables have the Subjects as headers and each subject column list down the levels(on first table) and trainers(on 2nd table) that are available for each said subject. And i have named the tables (Levels and Trainers) for referencing.

Answer 1

See below code snippet. On combobox change event, clear the combo box event and populate the new items in the desired combo box.

Private Sub cmblevels_Change()
    cmbTrainer.Clear
    Select Case LCase$(cmblevels.Text)
        Case "beginner"
            cmbTrainer.AddItem "trainer1"
            cmbTrainer.AddItem "trainer2"
        Case "advanced", "hard"
            cmbTrainer.AddItem "trainer3"
            cmbTrainer.AddItem "trainer4"
            cmbTrainer.AddItem "trainer5"
        Case "easy", "medium"
            cmbTrainer.AddItem "trainer3"
            cmbTrainer.AddItem "trainer6"
            cmbTrainer.AddItem "trainer7"
    End Select
End Sub

Private Sub cmbSubjects_Change()

    cmblevels.Clear
    Select Case LCase$(cmbSubjects.Text)
        Case "english"
            cmblevels.AddItem "beginner"
            cmblevels.AddItem "advanced"
        Case "maths"
            cmblevels.AddItem "easy"
            cmblevels.AddItem "medium"
            cmblevels.AddItem "hard"
    End Select

End Sub

Answer 2

I have also found the Select Case function helpful with this problem. Where i created named ranges in my "Info" worksheet and i used those ranges for the list of what i needed to appear in my Levels combo box and Trainers combo box. Code below works and if you will notice, the Trainer Name combo box is in another userform.

 Private Sub cmbSubject_Change()
     Me.cmbLevel = ""

     Select Case Me.cmbLevel
         Case "English"
             Me.cmbLevel.RowSource = "English"
             frmTrName.cmbTrSubj.RowSource = "trEng"

         Case "Math"
             Me.cmbLevel.RowSource = "Math"
             frmTrName.cmbTrSubj.RowSource = "trMath"

         Case "Sciences"
             Me.cmbLevel.RowSource = "Sciences"
             frmTrName.cmbTrSubj.RowSource = "trSci"
     End Select

 End Sub

Hope this helps!