Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Accessing data in drop-down menus (EXcel 2003)

    Hi all....I have a number of sheets where I am using Data Validation and a list with drop-down menus....I click the drop-down and then scroll thru the list to select the name I want etc etc....I have to do this using my mouse (as far as I know...)......is there a way to open the drop-down and select names without using a mouse (ie: using the arrow keys, for example) ? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Use the arrow keys to navigate to a cell with a validation dropdown arrow.
    Press Alt+down arrow to drop down the list.
    Use the up and down arrow keys to select an item.
    Press Enter to confirm the selection.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    When you press the arrow to open the validation list is their a way to start typing the item in the list you want and to go to the start letter or to have the list filter based on what you start typing.
    example.... the item I am looking for is GSJ2560 and my list is 1000 rows long....I would like to start typing the G and have the list filter out showing all of the entries starting with G the when I type the S the list gets smaller showing only the GS entries and so on...so that if I get to the J and I only have 1 entry starting with GSJ then it is the only thing shown on the list

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    No, validation doesn't work that way, unfortunately - it would have been nice!

    Combo boxes from the Control Toolbox work the way you want - you can start typing, and the list will match what you type.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    But can a combo box be linked to 1 cell so the information can be used like a cell.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Yes, it has the LinkedCell property which can be set to the desired cell. A combobox is much more versatile than datavalidation.


    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Yes. When you place a combo box from the Control Toolbox on a sheet, design mode will automatically be switched on.
    With the combo box still selected, click the Properties button on the Control Toolbox (the second button).
    You can enter the address of the range that acts as list in the ListFillRange property, and the address of the cell that will contain the selected item in the LinkedCell property.
    If you wish, you can position the combo box over the cell it is linked to.
    To test the combo box, switch off design mode using the first button on the Control Toolbox.
    If you want to modify its properties later, you can switch design mode on again.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing data in drop-down menus (EXcel 2007)

    I have just tried the combo box with a list of 5 names in a column I can get it to populate a cell but something happens my list is of names the combo box shows the name I choose but the cell is populated with a number instead of the name

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Are you sure that you used a combo box from the Control Toolbox? The behavior you describe is for a combo box from the Forms toolbar.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Should it be the ActiveX Combo box..... I am using 2007 version

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Yes, you should use the ActiveX combo box (I only now noticed that you changed the version in the subject)

  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Sorry about that. I only noticed it was 2003 in the last message...... now how do I have it start making the list shorter as I type so I see smaller amounts in the list the more of the code I type in

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    It doesn't work like that. As you type, the list will jump to the first item matching what you typed.

  14. #14
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    Their is no way to filter as you type..... that would have been helpfull as some of these product codes are very cryptic

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Accessing data in drop-down menus (EXcel 2003)

    A better way may be to have multiple dependent lists. Select a category (for example) and then have a second list pop up that is dependent on the first. YOu could have several lists ewach cascading to the selection rather than a single large list.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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