Excel: 4 Dependent Combo Boxes


Hi Guys, I hope you’ll be able to assist me with the below code. I’m trying to build 4 d active dependent Combo Boxes. I got great assistance from one of the forum members here. However, I’m afraid the code works perfectly till “Case 3000”, starting from this case, it doesn’t work properly at all. Can someone check it for me? What is missing here? Additionally, I need to know how to assign such macro to a button for example to trigger it?

Private Sub UserForm_Initialize()
 
With ComboBox1
    .AddItem "Placement Tests"
    .AddItem "Courses"
    .AddItem "Miscellaneous"
End With
 
End Sub
 
 
 
 
Private Sub ComboBox1_Change()
 
ComboBox2.Clear
 
Select Case ComboBox1.ListIndex
    Case 0
        With ComboBox2
            .AddItem "Individual Adults"
            .AddItem "Teens"
            .AddItem "Corporates"
        End With
    Case 1
        With ComboBox2
            .AddItem "Individual Adults"
            .AddItem "Teens"
            .AddItem "Kids"
            .AddItem "Corporates"
        End With
    Case 2
        With ComboBox2
            .AddItem "X Course Deposit Paying"
            .AddItem "X Course Booking"
            .AddItem "Payment Posting (for Any Reason)"
            .AddItem "Getting Copy of the Booking Confirmation"
            .AddItem "Getting Copy of the Payment Receipt"
        End With
End Select
 
End Sub
 
 
 
 
Private Sub ComboBox2_Change()
 
ComboBox3.Clear
 
Select Case ComboBox1.ListIndex * 1000 + ComboBox2.ListIndex
    Case 0
        With ComboBox3
            .AddItem "Reservation"
            .AddItem "Booking"
        End With
    Case 1
        With ComboBox3
            .AddItem "Reservation"
            .AddItem "Booking"
        End With
    Case 2
        With ComboBox3
            .AddItem "Single Reservation"
            .AddItem "Multiple Reservation"
            .AddItem "Single Booking"
        End With
    Case 1000
        With ComboBox3
            .AddItem "Reservation"
            .AddItem "Booking"
            .AddItem "Waiting List"
        End With
    Case 1001
        With ComboBox3
            .AddItem "Reservation"
            .AddItem "Booking"
            .AddItem "Waiting List"
        End With
    Case 1002
        With ComboBox3
            .AddItem "Reservation"
            .AddItem "Booking"
            .AddItem "Waiting List"
        End With
    Case 1003
        With ComboBox3
            .AddItem "Reservation"
            .AddItem "Booking"
            .AddItem "Waiting List"
        End With
End Select
 
End Sub
 
 
 
 
Private Sub ComboBox3_Change()
 
ComboBox4.Clear
 
Select Case ComboBox1.ListIndex * 1000 + ComboBox2.ListIndex * 1000 + ComboBox3.ListIndex
    Case 0
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 1
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "(By Credit Redemption) Confirmation"
            .AddItem "For Young Learner for IELTS Preparation Course Confirmation"
            .AddItem "Cancellation"
            .AddItem "Cancellation (with Lost Payment Receipt)"
            .AddItem "Cancellation (after Credit Redemption)"
            .AddItem "Changing or Taking Recommended B or C Tests"
        End With
    Case 1000
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 1001
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "(By Credit Redemption) Confirmation"
            .AddItem "Cancellation"
            .AddItem "Cancellation (with Lost Payment Receipt)"
            .AddItem "Cancellation (after Credit Redemption)"
            .AddItem "Changing"
        End With
    Case 2000
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 2001
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 2002
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing or Taking Recommended B or C Tests"
        End With
    Case 3000
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 3001
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Confirmation (Academic Writing or IELTS Preparation)"
            .AddItem "Confirmation (by Credit Redemption)"
            .AddItem "Confirmation (by Credit Redemption) (Academic Writing or IELTS Preparation)"
            .AddItem "Cancellation (before Term Start)"
            .AddItem "Cancellation (before Term Start) by 3rd Party"
            .AddItem "Cancellation (before Term Start) (with Lost Payment Receipt)"
            .AddItem "Cancellation (before Term Start) (with Lost Payment Receipt) by 3rd Party"
            .AddItem "Cancellation (before Term Start) (after Credit Redemption)"
            .AddItem "Cancellation (before Term Start) (after Credit Redemption) by 3rd Party"
            .AddItem "Cancellation (after Term Start)"
            .AddItem "Cancellation (after Term Start) by 3rd Party"
            .AddItem "Cancellation (after Term Start) (with Lost Payment Receipt)"
            .AddItem "Cancellation (after Term Start) (with Lost Payment Receipt) by 3rd Party"
            .AddItem "Cancellation (after Term Start) (after Credit Redemption)"
            .AddItem "Cancellation (after Term Start) (after Credit Redemption) by 3rd Party"
            .AddItem "Changing (before the end of 2nd Lecture)"
            .AddItem "Changing (before the end of 2nd Lecture) by 3rd Party"
            .AddItem "Changing (after the end of 2nd Lecture)"
            .AddItem "Changing (after the end of 2nd Lecture) by 3rd Party"
        End With
    Case 3002
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 4000
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 4001
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Confirmation (by Credit Redemption)"
            .AddItem "Cancellation (before Term Start)"
            .AddItem "Cancellation (before Term Start) by 3rd Party"
            .AddItem "Cancellation (before Term Start) (with Lost Payment Receipt)"
            .AddItem "Cancellation (before Term Start) (with Lost Payment Receipt) by 3rd Party"
            .AddItem "Cancellation (before Term Start) (after Credit Redemption)"
            .AddItem "Cancellation (before Term Start) (after Credit Redemption) by 3rd Party"
            .AddItem "Cancellation (after Term Start)"
            .AddItem "Cancellation (after Term Start) by 3rd Party"
            .AddItem "Cancellation (after Term Start) (with Lost Payment Receipt)"
            .AddItem "Cancellation (after Term Start) (with Lost Payment Receipt) by 3rd Party"
            .AddItem "Cancellation (after Term Start) (after Credit Redemption)"
            .AddItem "Cancellation (after Term Start) (after Credit Redemption) by 3rd Party"
            .AddItem "Changing (before the end of 2nd Lecture)"
            .AddItem "Changing (before the end of 2nd Lecture) by 3rd Party"
            .AddItem "Changing (after the end of 2nd Lecture)"
            .AddItem "Changing (after the end of 2nd Lecture) by 3rd Party"
        End With
    Case 4002
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 5000
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 5001
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Confirmation (by Credit Redemption)"
            .AddItem "Cancellation (before Term Start)"
            .AddItem "Cancellation (before Term Start) by 3rd Party"
            .AddItem "Cancellation (before Term Start) (with Lost Payment Receipt)"
            .AddItem "Cancellation (before Term Start) (with Lost Payment Receipt) by 3rd Party"
            .AddItem "Cancellation (before Term Start) (after Credit Redemption)"
            .AddItem "Cancellation (before Term Start) (after Credit Redemption) by 3rd Party"
            .AddItem "Cancellation (after Term Start)"
            .AddItem "Cancellation (after Term Start) by 3rd Party"
            .AddItem "Cancellation (after Term Start) (with Lost Payment Receipt)"
            .AddItem "Cancellation (after Term Start) (with Lost Payment Receipt) by 3rd Party"
            .AddItem "Cancellation (after Term Start) (after Credit Redemption)"
            .AddItem "Cancellation (after Term Start) (after Credit Redemption) by 3rd Party"
            .AddItem "Changing (before the end of 2nd Lecture)"
            .AddItem "Changing (before the end of 2nd Lecture) by 3rd Party"
            .AddItem "Changing (after the end of 2nd Lecture)"
            .AddItem "Changing (after the end of 2nd Lecture) by 3rd Party"
        End With
    Case 5002
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 6000
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
    Case 6001
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation (before Term Start)"
            .AddItem "Changing (before the end of 2nd Lecture)"
            .AddItem "Changing (After the end of 2nd Lecture)"
        End With
    Case 6002
        With ComboBox4
            .AddItem "Confirmation"
            .AddItem "Cancellation"
            .AddItem "Changing"
        End With
 
End Select
 
End Sub


This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of March 18, 2018.


For more resources for Microsoft Excel