Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    LIst box in the worksheet? (Excel 2000)

    For my worksheet, I'm wanting to create a row that has a series of drop down lists.
    For the worksheet, we want the user to use from a set list of headings. So, how can a make the cells in row 1 into list boxes (that I will of course populate)?

    New to excel, Jody

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

    Re: LIst box in the worksheet? (Excel 2000)

    Have you tried Data | Filter | AutoFilter?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: LIst box in the worksheet? (Excel 2000)

    Ok, one step close (smile)....
    Now, I don't want my user to see the list, but only pick from the list. I noticed that I was asked if I wanted top 10, all, etc...
    but when I selected all, I once again hand the whole list visible.

    -J

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

    Re: LIst box in the worksheet? (Excel 2000)

    Jody,

    There are several possibilities, so in order to be able to give advice, we need to know in more detail what you want to accomplish. Do you want to limit what the user can enter in a cell, or ...?

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Edison, New Jersey, USA
    Posts
    215
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: LIst box in the worksheet? (Excel 2000)

    Yes, I want to limit what the user enters into a cell to a specific list. The Item that they choose will become the heading for that column, which will impose properties to that column.

  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: LIst box in the worksheet? (Excel 2000)

    You will have to be more specific about what you want.

    Datafilter filters the list in place.
    If you want the pulldown list for this data on another sheet you can (there are other options, here are just some thoughts)
    1) use datavalidation, with the list being the datarange and a column from the other sheet. This will not display the unique items. It will display them all in the order you have them.

    2) you could have a routine extract the unique items from each column and copy it a separate sheet and then sort them. Then the datavalidation could be linked to these intermediate columns. You would have to trigger the code to update to make sure the lists were always "up-to-date" (perhaps on the datarange sheet deactivation, so when you leave the sheet , it would update the lists)

    also you could use the unique sorted list created with a forms or control toolbox combobox and use the range as the "fill range". You could have the macro triggered with the deactivation event or even (to ensure always up-to-date) when the combobox is selected.

    3) Add comboboxes to the sheet and when you press the pulldown, run a routine to extract the unique sorted list from the appropriate column of the data range and then fill the combobox with the items in the list. This eliminates the need for an "intermediate list"

    Is this the type of thing that you are after or am I way off-base?

    Steve

Posting Permissions

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