Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create drop down box in Excel 2K (Excel 2K)

    Hi,

    Can anyone tell me how I go about creating a drop down list in Excel?
    I want to take the list from one worksheet and have it display in a drop-down list box in another so that I can choose from the list . The selected text from the list will then be added to the cell .

    Does this have to be done in VBA (Haven't got a clue on how to program in VBA, but any examples would be appreciated!), or is there a procedure in Excel to do this?


    Thanks,
    Ivan Weinstein <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Create drop down box in Excel 2K (Excel 2K)

    See if Data Validation works for you. There is a limitation in that the source list (of "allowed" data choices), must be in the same workbook; Help tells you it must be in the same spreadsheet, but I think you can get around this by using a range name for the source list and placing the list on a hidden sheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create drop down box in Excel 2K (Excel 2K)

    You can create a drop down list easy enough if you use the Control Toolbox menu. If you do not have it displayed you can access it by going to Tools, Customise and go through the list of Toolbars until you find one called Control Toolbox. Make sure it is checked, and the toolbar will be visible. The first item (normally) is a toggle for setting design mode on and off. Go through the rest of the items until the tip text says CombBox. Click on that and then you can create a ComboBox (drop down list) on the worksheet where you require. When you have it in place right click on it, and select Properties. A list of all the properties available should be made visible. The ones you are most interested in are the Linked Cell and the ListFillRange. If you are just interested in setting the value for a single cell using the dropdown list, you can enter the cell reference (e.g. A1) as the Linked Cell . You can enter the range of the data on the other worksheet as the ListFillRange, for example if the data is in cells A1 to A20 in Sheet2, you would enter Sheet2!A1:A20. To rest everything toggle design mode off and click on your drop down list to select a value. Hopefully you will have the value placed in the chosen cell.

    If you need to have the active cell given the value instead of a preset cell, you will need some little VBA code to do that. Toggle design mode on, right click on the dropdown list and select View Code. You should now be in the VBA editor. And you will probably see the outline of a subroutine. Delete what ever text is there and enter the following :<pre>Private Sub ComboBox1_DropButtonClick()
    Me.ComboBox1.LinkedCell = ActiveCell.Address
    End Sub</pre>

    Close the VBA editor and return to you worksheet. Now the dropdown list should populate the active cell with the selected value.

    Andrew C

  4. #4
    Lounger
    Join Date
    Jun 2001
    Location
    East Lansing, MI, Michigan, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create drop down box in Excel 2K (Excel 2K)

    I have a co-worker with the opposite problem--or is it? Anyway, he's made some lists and can't get them to go away. What he really wants to do is merge the 2-column lists from several sheets and then sort them. But he can't sort them, because the individual lists he pastes one below the other stick together! I gave him a "workaround:" paste into notepad, then back to excel. List gone, but very inelegant . . .is there a better way? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create drop down box in Excel 2K (Excel 2K)

    Sorry, but coul dbe more specific. From where are the lists being copied, ? a worksheet ?.

    And what do you mean by 'stick together'. I'm just trying to envision what exactly is happening.

    Andrew C

  6. #6
    Lounger
    Join Date
    Jun 2001
    Location
    East Lansing, MI, Michigan, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create drop down box in Excel 2K (Excel 2K)

    What

Posting Permissions

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