4 Dependent Combo Boxes

M

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
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?


Code: 
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
 
Sort by date Sort by votes

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
M
  • Question Question
3 Dependent Combo Boxes
Replies
6
Views
1K
Magdoulin
M
Oberon70
  • Question Question
Thoughts on how to proceed or accomplish a batch report being created
Replies
0
Views
512
Oberon70
Oberon70
D
  • Question Question
Error '1004' Sort method of range class failed
Replies
0
Views
1K
Davavo
D
V
  • Question Question
To dynamically display Text based on dependent combo box selection on a Button click
Replies
1
Views
1K
Roderick_E
Roderick_E

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top