+ Reply to Thread
Results 1 to 14 of 14

Excel 2007 : How to create drop down list but options reducing after every seclection?

  1. #1
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

    Question How to create drop down list but options reducing after every seclection?

    Hi,

    Is it possible to do a drop down list that allow user to select pre-defined selections but everytime when a selection is chosen, the list reduced?

    Example,

    I have can choose Apple, Orange, Durian and Mango.
    The drop down can only select these 4 fruits.

    So there are 4 cells in Column A1, A2, A3, A4.
    A1 selects Mango and then A2 will only have 3 options to choose. So until the last cell, he can only choose the last fruit left.

    A1, A2, A3, A4 are not selecting in descending order thus it could be A3 selecting first, then A2 and then A4.

    I'm using excel 2007. Hope someone can enlighten me. Thanks.
    Last edited by Lewis Koh; 08-22-2011 at 10:06 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to create drop down list but options reducing after every seclection?

    Try this workbook, select from the drop-downs in Column C.
    It uses the worksheet change event to determine what remains in the dropdown list.

    Hope this helps.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

    Re: How to create drop down list but options reducing after every seclection?

    Hi, oh yes. This is it. May I know how do you make the drop list work like this? Where do I do the change event stuff?

    Hi, I managed to see the code now. I see that the pilots are pre-defined in the code. Is there a way to do it in such a way that we can add and remove the pilot's name without going into the codes? What I'm doing is that I have hundreds or thousands of items to be put into a list for selection and it changes every now and then.
    Last edited by Lewis Koh; 08-17-2011 at 05:52 AM. Reason: Managed to find from internet what is "Worksheet Change Event"

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to create drop down list but options reducing after every seclection?

    Try this.

    Make a dynamic named range, anywhere in your workbook, that contains all your unique values.
    This list must be continuous, i.e. no blanks.
    It need not be sorted, but it is better that it is.
    e.g.
    Formulas > Name Manager ...
    Name:= "Unique_List"
    Refers To:=
    Please Login or Register  to view this content.
    You can add to, subtract from, or amend this list as required, just don't leave any blanks.

    Note
    Any changes made to the list will not be reflected in Column C if their values have been entered before the changes were made.

    I have amended the code to use this named range, see the attached workbook.

    This will slow down considerably if you are talking of thousands of unique values.

    You will no doubt need to change the Ranges in the code to suit your sheet layout.
    These changes need only be made in the Sub "Worksheet_Change"
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-17-2011
    Location
    Findlay,Ohio
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: How to create drop down list but options reducing after every seclection?

    I've tried this but it does not seem to work.

    When I hit the drop down arrow nothing opens up.

    I am using a conditional Drop down with this to.

    If someone picks QB from a drop down in cell B2 only my list of Quarterbacks is shown.

    Can I not use conditional drop downs with this??

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to create drop down list but options reducing after every seclection?

    Hi Vack

    Welcome to the Forum ...
    Read this Forum Rules before the moderators get you...

    Forum Rules
    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

    If you can Edit/Delete your post, do so, if not you'll need to ask a moderator to help you.

    Attach a sample of your workbook to the new post.
    PM me when you have done this and I'll have a look at what you are trying to do.

    Cheers

  7. #7
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

    Question Re: How to create drop down list but options reducing after every seclection?

    Hi,

    My list actually contains computer model numbers, thus the ReducingChoiceInList.xls is rather different.

    Is there a list to arrList = Array? Can I house thousands of characters example, X200, T61, T420, etc etc?

    I was thinking of transposing all the information which is stored in rows like A1, A2, A3, A4 to A1, B1, C1, D1 and then adding "xx" and "," and make them into a string in one cell then copy into the arrlist. Is it possible to do that?
    Last edited by Lewis Koh; 08-17-2011 at 09:06 PM. Reason: thinking of alternative.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to create drop down list but options reducing after every seclection?

    Best if you could post a workbook with some typical data, and what you need done with it.
    We could then see what has to be done.

    Are you looking to break down the lists into smaller ranges?
    Thousands of items in a drop-down list isn't very practical from a users' point of view.

  9. #9
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

    Question Re: How to create drop down list but options reducing after every seclection?

    Hi, I have attached a sample of the systems (selection list) that I have. I need to let multiple users choose the systems they want. Attached is just models from Acer and Lenovo/IBM and I have not include Sony, Toshiba, HP, Dell, thus the selections could be in thousands included repeated models.

    My idea was to maintain the database without registering them manually to the excel whenever a system is issued out.

    Thanks for looking into it.
    Attached Files Attached Files

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to create drop down list but options reducing after every seclection?

    Did you miss post #4?

    Despite what Vack says, it does work.

    Here is the workbook with your data added, sorted, and duplicates removed.

    Read Post #4 in conjunction with this workbook.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

    Question Re: How to create drop down list but options reducing after every seclection?

    Hi,

    I tried shifting the Unique_List to sheet 2 and rename it to system list. When I try to select from the drop box, it displayed "Runtime error 1004", "Method "Range" of object "_Worksheet" Failed.

    Is it a limitation of excel not able to use the Unique List on another worksheet?

    Attached is my sample.
    Attached Files Attached Files

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: How to create drop down list but options reducing after every seclection?

    Unfortunatly VBa doesn't recognise dynamic names outwith the sheet it is written for
    i.e. Unlike Excel, it cannot recognise where a named range is within a workbook.

    We have to tell VBa what sheet it is in
    Change the worksheet module code to this (Sheet "Selection")
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    07-29-2005
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    253

    Re: How to create drop down list but options reducing after every seclection?

    Thanks Marcol. It works! :-)

  14. #14
    Registered User
    Join Date
    01-03-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Excel 2007 : How to create drop down list but options reducing after every seclection?

    I have used the information above, and it works wonderfully. I had some trouble, but it was all user-induced mental constipation.

    Thank you for sharing the code... It makes my project worthwhile!

    K7
    Last edited by Kobra7; 03-27-2014 at 05:29 PM. Reason: corrected my problem.

+ 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