Error Checking in Excel
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: 4 Dependent Combo Boxes

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 4 Dependent Combo Boxes

    Hi Guys,


    I hope you値l be able to assist me with the below code.

    I知 trying to build 4 d active dependent Combo Boxes.

    I got great assistance from one of the forum members here.

    However, I知 afraid the code works perfectly till 鼎ase 3000, starting from this case, it doesn稚 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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,438
    Post Thanks / Like
    Mentioned
    247 Post(s)
    Tagged
    17 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    Your initial problem is with Combobox2 (witch can be written like)
    Code:
    Private Sub ComboBox2_Change()
     
    ComboBox3.Clear
     
    Select Case ComboBox1.ListIndex * 1000 + ComboBox2.ListIndex
        Case 0, 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 To 1003
            With ComboBox3
                .AddItem "Reservation"
                .AddItem "Booking"
                .AddItem "Waiting List"
            End With
    End Select
     
    End Sub
    As Cbox1 has 3 items, if the 3rd item is selected this line
    Code:
    Select Case ComboBox1.ListIndex * 1000 + ComboBox2.ListIndex
    will give you a value of 2000+, which you haven't accounted for.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Jan 2013
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    You mean mistake with ComboBox3 then

    Well, I've proceeded with your suggested adjustments anyways

    I'm afraid that the same issue still persists

    It doesn't match properly when it comes to ComboBox4 starting from "Courses / Adults / Booking" selection

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,438
    Post Thanks / Like
    Mentioned
    247 Post(s)
    Tagged
    17 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    You mean mistake with ComboBox3
    No, I mean the initial mistake is with Cbox 2.
    If you select "Miscellaneous" in Cbox1 (which is index 2), when you change Cbox 2 this line
    Code:
    Select Case ComboBox1.ListIndex * 1000 + ComboBox2.ListIndex
    is saying 2*1000 +Cbox2 ie 2000 & something, but your code only deals with 1000 & something.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Jan 2013
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    Well, this's for the fact that the purpose here is that after selecting "Miscellaneous", ComboBox2 should give 5 options yes, but there'll be no further options to choose from ComBox3 in this case. Basically, ComboBox3 & ComboBox4 are here for the rest of the options, but not for this case which stops at ComboBox2

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,438
    Post Thanks / Like
    Mentioned
    247 Post(s)
    Tagged
    17 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    In that case your code seems to work for me, what is not working?
    If you are happy using the list from your similar thread on Ozgrid, you could use this code instead
    Code:
    Option Explicit
    Dim Dic As Object
    
    Private Sub UserForm_Initialize()
    
       Dim Cl As Range
       Dim v1 As String, v2 As String, v3 As String, v4 As String
    
       Set Dic = CreateObject("scripting.dictionary")
       Dic.CompareMode = vbTextCompare
       With Sheets("Lists")
          For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
             v1 = Cl.Value: v2 = Cl.Offset(, 1).Value: v3 = Cl.Offset(, 2).Value: v4 = Cl.Offset(, 3).Value
             If Not Dic.exists(v1) Then
                Dic.Add v1, CreateObject("scripting.dictionary")
                Dic(v1).Add v2, CreateObject("scripting.dictionary")
                Dic(v1)(v2).Add v3, CreateObject("scripting.dictionary")
                Dic(v1)(v2)(v3).Add v4, Nothing
             ElseIf Not Dic(v1).exists(v2) Then
                Dic(v1).Add v2, CreateObject("scripting.dictionary")
                Dic(v1)(v2).Add v3, CreateObject("scripting.dictionary")
                Dic(v1)(v2)(v3).Add v4, Nothing
             ElseIf Not Dic(v1)(v2).exists(v3) Then
                Dic(v1)(v2).Add v3, CreateObject("scripting.dictionary")
                Dic(v1)(v2)(v3).Add v4, Nothing
             ElseIf Not Dic(v1)(v2)(v3).exists(v4) Then
                Dic(v1)(v2)(v3).Add v4, Nothing
             End If
          Next Cl
       End With
       ComboBox1.List = Dic.keys
       
    End Sub
    Private Sub ComboBox1_AfterUpdate()
       ComboBox2.Clear
       ComboBox2.List = Dic(ComboBox1.Value).keys
    End Sub
    Private Sub ComboBox2_AfterUpdate()
       ComboBox3.Clear
       ComboBox3.List = Dic(ComboBox1.Value)(ComboBox2.Value).keys
    End Sub
    Private Sub ComboBox3_AfterUpdate()
       ComboBox4.Clear
       ComboBox4.List = Dic(ComboBox1.Value)(ComboBox2.Value)(ComboBox3.Value).keys
    End Sub
    Test file is here https://1drv.ms/x/s!AtYrlcV2NWPFgQ65c22ryuUWU5S-
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Jan 2013
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    The project has been solved in Ozgrid but depending on assisting sheets to let the ComBoxes read their values from it.

    I'm seeking for simpler solution, by using VBA to apply the values for ComBoxes directly

    My code works perfectly till you reach the point selecting (Courses / Individual / Reservation), if you try it you'll get the values of selecting Placement Test / Individual / Reservation), it'll be obvious when selecting for example (Courses Individual / Booking) cause this track later options are totally different, they're kind of 19 sub-options, and they don't appear if you try

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,438
    Post Thanks / Like
    Mentioned
    247 Post(s)
    Tagged
    17 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    If I select Courses / Individual / Reservation, I get Confirmation / Cancellation / Changing
    If I select Courses / Kids / Reservation, I get the same output, which is what your code says it should be.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    New Member
    Join Date
    Jan 2013
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    Yeah, because they're identical to Placement Test / Individual / Reservation later options
    I tell you what, let us change case 3000 options in the VBA Code itself to be anything else, like A, B, C for instance instead of Confirmation / Cancellation / Changing
    Case 3000 is related to this selection Courses / Individual / Reservation
    Now you'll see what I'm talking about, starting from this point, it won't work

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,438
    Post Thanks / Like
    Mentioned
    247 Post(s)
    Tagged
    17 Thread(s)

    Default Re: 4 Dependent Combo Boxes

    If I select Courses, Corporate, bookings. I get the selection for Case 4001, which is correct.
    That said I have noticed you have Case 5000 and beyond, which is redundant as you can only get a maximum of 4002
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •