+ Reply to Thread
Results 1 to 17 of 17

Dropdown list with more than eight items

  1. #1
    Registered User
    Join Date
    09-15-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Dropdown list with more than eight items

    Hi all. I need to create a dropdown list that can handle more than the standard eight rows that data validation allows. My list requires 75 different items in the dropdown. Is there any way that I can do this? I hope someone can help!
    JLB.
    Last edited by johnlovesbeer; 10-15-2009 at 07:05 AM. Reason: Solved

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dropdown list with more than eight items

    I presume you're referring to the number of items Displayed as opposed to number of items Contained within the list given a Validation List can hold a large number of options.
    If that is indeed the case then I'm afraid you will need to look at using VBA based Controls like ComboBox in which you can specify rows to display.

    For an illustration of the above see: http://www.contextures.com/xlDataVal10.html
    Last edited by DonkeyOte; 10-05-2009 at 05:52 AM. Reason: added link to DD site

  3. #3
    Registered User
    Join Date
    09-15-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Dropdown list with more than eight items

    Thanks DonkeyOte, that fixed the basic problem. Now I need to copy that combo box to another 2000 cells in the same column. Is this possible? I can't just grab and drag like a standard data validation dropdown box. I hope I don't have to paste each one individually.
    Many thanks for the first advice!
    Cheers,
    JLB.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dropdown list with more than eight items

    You would need to copy and paste them. I would also very strongly advise against it. What exactly is the issue with scrolling the validation dropdown list, if I may ask?
    Remember what the dormouse said
    Feed your head

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dropdown list with more than eight items

    I'm afraid so... moreover you need to ensure each has the linked cell updated appropriately.

    Perhaps you should think of an alternative approach ?

    What about using a User Form such that when the user double clicks on a cell (where you want the list) a Form appears with the ComboBox from which they can make their choice and write back to the cell... this way you have only one combobox to manage.

    See attached as a basic illustration of my thoughts...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-15-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Dropdown list with more than eight items

    In reply to Romper, my dropdown needs 70 different items listed so it's a bit too much for the data validation option.

    In reply to DonkeyOte (great name!) the user form looks like the perfect solution. Is there an easy way to create one without using VBA. I'm fairly new to this and VBA looks a little daunting!

    Again many thanks,

    JLB.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dropdown list with more than eight items

    Just to reiterate, a validation list can hold 70 values it is simply restricted in so far as

    a) it can only display 8 rows at any given time

    b) it does not have auto complete feature

    Re: User Form - VBA is a necessity I'm afraid.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dropdown list with more than eight items

    It is a good name though...

  9. #9
    Registered User
    Join Date
    09-15-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Dropdown list with more than eight items

    Sorry DonkeyOte, I'm explaining myself poorly. I need a dropdown list with 70 rows. Looks like it's VBA. I think I'll be posting more questions!

    Thanks for all the help,

    Cheers, JLB.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dropdown list with more than eight items

    The DV dropdown can have 70 rows (and a lot more) it's just that only 8 are visible at one time - you have to scroll to see the others. To be honest, I don't see what benefit you would get from having all 70 visible at once. (though I can see the advantage of the autocomplete, which is tricky to implement with DV.)

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dropdown list with more than eight items

    Quote Originally Posted by romperstomer
    It is a good name though...
    Yes, it is... credit to whomever it was that suggested it... ... not getting me much joy on the CV front though !

    Quote Originally Posted by romperstomper
    ...though I can see the advantage of the autocomplete, which is tricky to implement with DV
    R, you imply it is "possible" then to implement AutoComplete with Validation - can you demo ?

  12. #12
    Registered User
    Join Date
    09-15-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Dropdown list with more than eight items

    Hi Romper, maybe I'm being a bit thick here! How do I create a scrolling dropdown that can handle 70 rows in DV? I can't find any options to do so! It's probably something simple that I can't see.

    Thanks, JLB.

  13. #13
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dropdown list with more than eight items

    If you use the file I provided to demo the DV...

    in a cell insert a Validation List - set Source to be =Options

    You should now see a List in the cell containing the 75 options, 8 will display but you can scroll.

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dropdown list with more than eight items

    Quote Originally Posted by DonkeyOte View Post
    Yes, it is... credit to whomever it was that suggested it... ... not getting me much joy on the CV front though !
    yes, it's one advantage of using a real name occasionally!
    R, you imply it is "possible" then to implement AutoComplete with Validation - can you demo ?
    Not strictly autocomplete, but you can filter the validation list based on what is typed - see here for example. In all honesty I would probably use userform for this instead though, if there were so many items as to require this!

  15. #15
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Dropdown list with more than eight items

    hmm... unfortunately I'm getting malware warning on that link (Chrome)

  16. #16
    Registered User
    Join Date
    09-15-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Smile Re: Dropdown list with more than eight items

    Thanks Guys, got the scrolling dropdown in DV working a treat! Brilliant!

    Once again, thanks for all the help. I will leave the thread open for a little while before I mark it as solved in case you solve the auto-complete issue!

    Cheers,
    JLB.

  17. #17
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Dropdown list with more than eight items

    It's a link to Colo's junk room, so I'm not sure what the issue is. (I have seen it before, but just ignore it, since I don't download or run anything from there.)

+ 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