Consulting

Results 1 to 6 of 6

Thread: Multiple conditions for 3 consecutive ListBox - How to make it work?

  1. #1
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    3
    Location

    Multiple conditions for 3 consecutive ListBox - How to make it work?

    Hello all,

    I'm trying to create a file with multiple selection in 3 consecutive listbox. The idea being that the first listbox allows you to narrow down the content of the second one and the combination of what you select in the first and the second listbox gives you the relevant content in the 3rd listbox. A bit like if you had 3 columns in Excel where you would first filter the first one, then the second one and then you would have the possibilities in the 3rd column popping-up.

    I have the file attached. But the macro doesn't seem to cross choices based on listbox 1 and listbox 2 to select what's being shown in listbox 3. I seem to miss a step in the code and I don't know where.

    More precisely, currently for example if i select Adele in listbox 1 and piano in listbox2, i should have A1, FA2, QA2 showing in listbox 3. But for now I get a long list of 14 possibilities popping-up and it's not what i need. it seems that the listbox3 is only depending on listbox2, not on the combination of listbox 1 and listbox 2.


    Have you got an idea where the issue is by any chance?

    Cheers,
    Geoffrey
    Attached Files Attached Files
    Last edited by SamT; 02-23-2021 at 09:56 PM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Try setting Temp to the Range Value...

    Use
     mondico(c.Offset(, 1).Value) = c.Offset(, 1).Value
    In all subs, instead of
    temp = c.Offset(, 1)
    mondico(temp) = temp
    I see that you are searching the entire column for the textbox value in every case. It will be much faster if you group all the Rows by the Profs in textbox 1 sub, then just search those rows.
    StartRow = Columns(1).Find(What:=textbox1, After:=Cells(Rows.Count, "A"), SearchDirection:=xlNext).Row
    EndRow = Columns(1).Find(What:=textbox1, After:=Cells(Rows.Count, "A"), SearchDirection:=xlPrevious).Row
    
    For each c in Range(Rows(StartRow & ":" & EndRow)).Columns(n)
    In fact, I dislike looping that much that I Sort+Unique the first column into an index list in column A and use that List as textbox1.list
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    3
    Location
    Thanks a ton SamT for helping!

    I tried using
     mondico(c.Offset(, 1).Value) = c.Offset(, 1).Value
    instead of
    temp = c.Offset(, 1)
    mondico(temp) = temp


    But it doesn't seem to make any difference. I still don't get the outcome I'm trying to obtain: I still have listbox3 not showing the items coming from the crossing of listbox 1 (profs) and listbox 2 (Cours).

    To come back to my example, using the new code, if i select Adel in listbox1 and piano in listbox2, i should see appear FA1, FA2, QA2 as options I can select in listbox3. But it's not the case and I see many options which shouldn't show-up as my database i have no line corresponding for example to Adele/piano/FA4 or Adele/pianao/Q1 etc.



    Really no clue how to fix that.

    I like how you're approaching the searching. In my case I don't have so many lines so no big deal but could help for bigger projects. So thks a ton!

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    It's because you are searching every cell in column cours. Either "Group" as I mentioned above, or compare Column A To Textbox 1 and Column B to Textbox 2
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    3
    Location
    Thank you so much! Got it. Will try the new approach.
    Really big thank you for taking the time.
    Cheers!

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You'd better not use multiselect Listboxes.
    I don't see why you should.
    Attached Files Attached Files

Posting Permissions

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