+ Reply to Thread
Results 1 to 14 of 14

Populating combobox 2 with items that match criteria from combobox 1

  1. #1
    Forum Contributor
    Join Date
    10-17-2008
    Location
    Vancouver
    MS-Off Ver
    2002 and XP
    Posts
    117

    Populating combobox 2 with items that match criteria from combobox 1

    Hello,

    Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

    if column a = bears and column b = colours of bears then

    when I select bears in combobox one, combobox 2 would populate with colors of bear.

    I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.


    I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

    Please advise

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Populating combobox 2 with items that match criteria from combobox 1

    Here are a couple of places to look:

    http://en.allexperts.com/q/Excel-105...es-other-1.htm

    http://www.ozgrid.com/VBA/dependent-combobox.htm

    or you can use the easier Data Validation:

    http://www.contextures.com/xlDataVal02.html
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Populating combobox 2 with items that match criteria from combobox 1

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Populating combobox 2 with items that match criteria from combobox 1

    You forgot the link you promised in the other post:

    http://www.excelforum.com/excel-prog...ing-match.html

  5. #5
    Forum Contributor
    Join Date
    10-17-2008
    Location
    Vancouver
    MS-Off Ver
    2002 and XP
    Posts
    117

    Re: Populating combobox 2 with items that match criteria from combobox 1

    okay, this is what I mean,

    looking at a list with two levels of classification, it will populate the 2nd combobox with only the items that match the criteria specified in the 1st combobox

    eg. If I select Big Cats in the 1st combobox, the 2nd combobox will offer the choices of those rows that have "Big Cats" in the first column, thus offering choices like "lynx","lion" etc.

    I am trying it this way as the real sheet as at least 200 items and I'll be darned if I'm gonna make that into a dependant list setup.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Populating combobox 2 with items that match criteria from combobox 1

    Try this code in the combobox1 change event

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    10-17-2008
    Location
    Vancouver
    MS-Off Ver
    2002 and XP
    Posts
    117

    Re: Populating combobox 2 with items that match criteria from combobox 1

    wow. Thank you. Exactly what I was looking for.

    Question, could you explain the code a little bit,

    especially the portion with references to B1 and B2


    and what is r mean?

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Populating combobox 2 with items that match criteria from combobox 1

    This needs the lists separating, but should be easier to maintain
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    10-17-2008
    Location
    Vancouver
    MS-Off Ver
    2002 and XP
    Posts
    117

    Re: Populating combobox 2 with items that match criteria from combobox 1

    BigBas,

    I've used your example as it will be easier on the user to add items rather then dealing with separated lists.

    the problem is that:
    1) for criteria that acutally do not have any items in the list, the macro hangs and excel crashes. E.g. if I select "dogs" and there is only "Big Cats" and "Bears" in the list, error occurs.

    Can you help with some error handling code so that combobox2 says "none availible" if that error occurs?

    2) can an evaluation portion be added so that:
    if the 7th column of r = "availilble" then it gets added to combobox2,
    else it doesn't get added?

    If you could provide any insight on this, I would be most appreciative

  10. #10
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Populating combobox 2 with items that match criteria from combobox 1

    Kuraitori:

    There are going to be a few approaches to best resolving your problem; it will be based on which works best for you.

    1. I personally think your best bet is to limit combobox selections to options in the list. This way, an end user cannot select an option that is not on the list, thereby resolving the problem. If you absolutely must allow that a user type in unavailable options, I have added an amended code. Instead of entering the code in the combobox_change() even, it will be entered in the combobox_lostFocus() event. The disadvantage to this is that the code does not fire until the entry in completed. In order to be complete, the user will have to click outside of the textbox.

    Please Login or Register  to view this content.
    2. I'm not sure I understand what you need. Can you please update your dummy data sample so I can see what you are looking for?

  11. #11
    Registered User
    Join Date
    07-17-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Populating combobox 2 with items that match criteria from combobox 1

    Hi - I am a bit new to VBA but have some background in programming so I think I can get this solution to work for me as well. The only problem I have here is that I need the combobox to look and act just like it would if it was a data validation list. The content of what is selected in the combobox is used by several functions throughout the workbook. How do I use this this solution given my situation?

  12. #12
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,157

    Re: Populating combobox 2 with items that match criteria from combobox 1

    aaanenson,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    12-07-2013
    Location
    Buttrio, Itally
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Populating combobox 2 with items that match criteria from combobox 1

    Hi there forum users! I'm acctually not and programer, but i have this smal job to be done and i found, that BigBas macro would work perfectly for this. But i'm struggling with my smal VBA knowledge to do two things: First split data between two sheets. List for Combobox1 is in Sheet1, and for Combobox2 in Sheet2. Both comboboxes are in Sheet1. Second thing is that if user extends the range with new data, he would have to update VBA code. Is there a way to put some code for checking the last row with data for both list.
    It's a little bit old thread, but i hope someone could help me. Thank you in advance

  14. #14
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Populating combobox 2 with items that match criteria from combobox 1

    boryz

    See post#12, pls.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1