3 Dependent Combo Boxes

M

Magdoulin

Board Regular
Joined
Jan 11, 2013
Messages
73
Good Morning Fellows,


I hope to find the needed assistance here.

My project has 3 different Combo Boxes
The 1st One is linked in Cell C1
The 1st One is linked in Cell D1
The 1st One is linked in Cell E1

I need to link them to be dependents ones
I wrote VBA Code as below

Code: 
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "X"
.AddItem "Y"
.AddItem "Z"
End With
End Sub
 
 
 
Private Sub ComboBox1_Change()
Dim Index As Integer
Index = ComboBox1.ListIndex
ComboBox2.Clear
Select Case Index
 
Case Is = 0
With ComboBox2
.AddItem "A"
.AddItem "B"
.AddItem "C"
End With
 
Case Is = 1
With ComboBox2
.AddItem "D"
.AddItem "E"
.AddItem "F"
End With
 
Case Is = 2
With ComboBox2
.AddItem "G"
.AddItem "H"
.AddItem "I"
End With
 
End Select
End Sub
 
 
 
 
Private Sub ComboBox2_Change()
Dim Index As Integer
Index = ComboBox2.ListIndex
ComboBox3.Clear
Select Case Index
 
Case "D1" = "A"
With ComboBox3
.AddItem "1"
.AddItem "2"
.AddItem "3"
End With
 
Case "D1" = "B"
With ComboBox3
.AddItem "4"
.AddItem "5"
.AddItem "6"
End With
 
Case "D1" = "C"
With ComboBox3
.AddItem "7"
.AddItem "8"
.AddItem "9"
End With
 
Case "D1" = "D"
With ComboBox3
.AddItem "10"
.AddItem "11"
.AddItem "12"
End With
 
Case "D1" = "E"
With ComboBox3
.AddItem "13"
.AddItem "14"
.AddItem "15"
End With
 
Case "D1" = "F"
With ComboBox3
.AddItem "16"
.AddItem "17"
.AddItem "18"
End With
 
Case "D1" = "G"
With ComboBox3
.AddItem "19"
.AddItem "20"
.AddItem "21"
End With
 
Case "D1" = "H"
With ComboBox3
.AddItem "22"
.AddItem "23"
.AddItem "24"
End With
 
Case "D1" = "I"
With ComboBox3
.AddItem "25"
.AddItem "26"
.AddItem "27"
End With
 
End Select
End Sub

But it seems that it doesn’t work with the 3rd Combo Box for some reason
Could you advise what is wrong with the code?

P.S. I know Dependent Data Validation Lists is an option but this sheet is sample prototype of another original sheet and it needs combo boxes, not data validation list option


Thank you
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Note that this:

Code: 
Case "D1" = "I"

Will never be true. That's comparing two strings ("D1" and "I") that are clearly not the same. I think you'd be better to check the selected text like this:

Code: 
Private Sub UserForm_Initialize()

With ComboBox1
    .AddItem "X"
    .AddItem "Y"
    .AddItem "Z"
End With

End Sub
Private Sub ComboBox1_Change()

ComboBox2.Clear

Select Case ComboBox1.Text
    Case "X"
        With ComboBox2
            .AddItem "A"
            .AddItem "B"
            .AddItem "C"
        End With
    Case "Y"
        With ComboBox2
            .AddItem "D"
            .AddItem "E"
            .AddItem "F"
        End With
    Case "Z"
        With ComboBox2
            .AddItem "G"
            .AddItem "H"
            .AddItem "I"
        End With
End Select

End Sub
Private Sub ComboBox2_Change()

ComboBox3.Clear

Select Case ComboBox2.Text
    Case "A"
        With ComboBox3
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
        End With
    Case "B"
        With ComboBox3
            .AddItem "4"
            .AddItem "5"
            .AddItem "6"
        End With
    Case "C"
        With ComboBox3
            .AddItem "7"
            .AddItem "8"
            .AddItem "9"
        End With
    Case "D"
        With ComboBox3
            .AddItem "10"
            .AddItem "11"
            .AddItem "12"
        End With
    Case "E"
        With ComboBox3
            .AddItem "13"
            .AddItem "14"
            .AddItem "15"
        End With
    Case "F"
        With ComboBox3
            .AddItem "16"
            .AddItem "17"
            .AddItem "18"
        End With
    Case "G"
        With ComboBox3
            .AddItem "19"
            .AddItem "20"
            .AddItem "21"
        End With
    Case "H"
        With ComboBox3
            .AddItem "22"
            .AddItem "23"
            .AddItem "24"
        End With
    Case "I"
        With ComboBox3
            .AddItem "25"
            .AddItem "26"
            .AddItem "27"
        End With
End Select

End Sub

WBD
 
Upvote 0
Well, first of all, thank you so much for the prompt answer, I believe it has sorted big part here
Yet, there’s a little problem I guess, what if there is a duplication?
I mean, now Combo Box 1 contains for example values of X, Y and Z
Combo Box 2 contains A, B and C if X was selected, A, D and E, if Y was selected, and D, E and F if Z was selected
Now, for Combo Box 3, how to specify case for A which is related to the 1st option X, not the one related to the 2nd option Y?

Is there a work around?
 
Upvote 0
I’ve tried something like that but it didn’t work neither

Code: 
Private Sub UserForm_Initialize()
 
With ComboBox1
    .AddItem "X"
    .AddItem "Y"
    .AddItem "Z"
End With
 
End Sub
Private Sub ComboBox1_Change()
 
ComboBox2.Clear
 
Select Case ComboBox1.Text
    Case "X"
        With ComboBox2
            .AddItem "A"
            .AddItem "B"
            .AddItem "C"
        End With
    Case "Y"
        With ComboBox2
            .AddItem "A"
            .AddItem "D"
            .AddItem "E"
        End With
    Case "Z"
        With ComboBox2
            .AddItem "D"
            .AddItem "E"
            .AddItem "F"
        End With
End Select
 
End Sub
Private Sub ComboBox2_Change()
 
ComboBox3.Clear
 
Select Case ComboBox1.Text And ComboBox2.Text
    Case “X” And "A"
        With ComboBox3
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
        End With
    Case “X” And "B"
        With ComboBox3
            .AddItem "4"
            .AddItem "5"
            .AddItem "6"
        End With
    Case “X” And "C"
        With ComboBox3
            .AddItem "7"
            .AddItem "8"
            .AddItem "9"
        End With
    Case “Y” And "A"
        With ComboBox3
            .AddItem "10"
            .AddItem "11"
            .AddItem "12"
        End With
    Case “Y” And "D"
        With ComboBox3
            .AddItem "13"
            .AddItem "14"
            .AddItem "15"
        End With
    Case “Y” And "E"
        With ComboBox3
            .AddItem "16"
            .AddItem "17"
            .AddItem "18"
        End With
    Case “Z” And "D"
        With ComboBox3
            .AddItem "19"
            .AddItem "20"
            .AddItem "21"
        End With
    Case “Z” And "E"
        With ComboBox3
            .AddItem "22"
            .AddItem "23"
            .AddItem "24"
        End With
    Case “Z” And "F"
        With ComboBox3
            .AddItem "25"
            .AddItem "26"
            .AddItem "27"
        End With
End Select
 
End Sub
 
Upvote 0
OK. Then perhaps switch back to using ListIndex but something like this:

Code: 
Private Sub ComboBox1_Change()

ComboBox2.Clear

Select Case ComboBox1.ListIndex
    Case 0
        With ComboBox2
            .AddItem "A"
            .AddItem "B"
            .AddItem "C"
        End With
    Case 1
        With ComboBox2
            .AddItem "D"
            .AddItem "E"
            .AddItem "F"
        End With
    Case 2
        With ComboBox2
            .AddItem "G"
            .AddItem "H"
            .AddItem "I"
        End With
End Select

End Sub
Private Sub ComboBox2_Change()

ComboBox3.Clear

Select Case ComboBox1.ListIndex * 1000 + ComboBox2.ListIndex
    Case 0
        With ComboBox3
            .AddItem "1"
            .AddItem "2"
            .AddItem "3"
        End With
    Case 1
        With ComboBox3
            .AddItem "4"
            .AddItem "5"
            .AddItem "6"
        End With
    Case 2
        With ComboBox3
            .AddItem "7"
            .AddItem "8"
            .AddItem "9"
        End With
    Case 1000
        With ComboBox3
            .AddItem "10"
            .AddItem "11"
            .AddItem "12"
        End With
    Case 1001
        With ComboBox3
            .AddItem "13"
            .AddItem "14"
            .AddItem "15"
        End With
    Case 1002
        With ComboBox3
            .AddItem "16"
            .AddItem "17"
            .AddItem "18"
        End With
    Case 2000
        With ComboBox3
            .AddItem "19"
            .AddItem "20"
            .AddItem "21"
        End With
    Case 2001
        With ComboBox3
            .AddItem "22"
            .AddItem "23"
            .AddItem "24"
        End With
    Case 2002
        With ComboBox3
            .AddItem "25"
            .AddItem "26"
            .AddItem "27"
        End With
End Select

End Sub

WBD
 
Upvote 0
Thank you again, you're so generous.
I though that I figure out the logic, however, when I applied the method in my original sheet, it didn’t work I don’t know why
Could you have a look for me?
Here’s the original sheet code
Its elements are different


P.S. The Code worked for Combo Box 1, Combo Box 2, and for Combo Box 3, it works only till it reaches Case 3000, starting from this point it doesn't work whatsoever


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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Macro not working after adding two new items
Replies
4
Views
93
myall_blues
myall_blues
I
  • Question
Clear picture from Image Box
Replies
1
Views
302
NdNoviceHlp
NdNoviceHlp
S
  • Question
VBA for Userform to add data values
Replies
0
Views
439
Strycure12
S
A
  • Question
expand code to search on userform more than column by selection from combobox
Replies
1
Views
488
abdelfattah
A
N
  • Question
Can't initialize a userform always saying invalid procedure or call error
Replies
0
Views
269
nmc
N

Forum statistics

Threads
1,214,641
Messages
6,120,695
Members
448,979
Latest member
DET4492

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