Excel Drop Down

[bont]

Does anyone know how to add a drop down box to an Excel XP (2002) worksheet, populated with a column from another?

 

[wwgmr]

Yes, What I have done in the past is to us the data validation. There are ways to place a Drop down box via VBA, but I found Data validation easier.
1 What I would do is in the cell we want the drop down we go to data on the tool bar. Then Validation.
2 Then from the drop down we choose List.
3 For the source you would put in your field range. (I would name the range first, so I can just enter that.)
4 click okay and you now have drop down box.

Now there are problems with this. You will not be able to enter custom info. It will only Except what is on the list.

The other way is to do it thru code and set the on click event to bring up range. I am sure if you want help with that some can post code that would help.

I have just found this to be quick and easy. You will have to adjust your named range if you increase that area. Our put count function in to expand the Source range as it grows.*I.E. You add more to your list to have in the drop down box.

Hope this helps

Eric L
MOUS Expert Excel
MOUS PowerPoint Outlook
A+

 

[MarrowGenx]

To place a dropdown box in the active cell, use the following code:

xlWorkbook.ActiveSheet.DropDowns.Add((xlWorkbook.Application.ActiveCell.Left), xlWorkbook.Application.ActiveCell.Top, (xlWorkbook.Application.ActiveCell.Width), xlWorkbook.Application.ActiveCell.Height).Select

With xlWorkbook.Application.Selection
.ListFillRange = "Memo!Q40:Q41"
.LinkedCell = "D3"
.DropDownLines = 2
.Display3DShading = False
.Value = 2
End With

xlworkbook is a vb object for the excel worksheet.
.listfillrange is the range that you want to appear in the dropdown box ("Memo" is the sheet name, "Q40:Q41" are the two cells i selected.
.linkedcell is the cell where the end value of the dropdown box will be stored (I used the activecell "D3&quot.
.dropdownlines is the number of visible lines that you can see in the dropdown box (I think).

Hope this helps.

 

[SkipVought]

Hi bont,

There are a number of ways to "skin a cat". Here's another way that assumes that whenever you select a cell in column A, a ComboBox will become visible with a list named MyList which is anywhere that you like...

1. Use the Control Toolbox (right click in the toolbar and select), select a ComboBox and place ANYWHERE on the sheet.

2. With the ComboBox selected, open the Properties.

3. Set the Visible property FALSE

4. Set the ListFillRange to MyList

5. Activate the VB Editor (Alt+F11)

6. In the sheet object corresponding to the sheet containing the ComboBox, enter the following Code...

Private Sub ComboBox1_Change()
    'when the ComboBox is changed, _
    place the selected value in the corresponding cell _
    make the ComboBox invisible and _
    clear the value in the ComboBox
    With ComboBox1
        .TopLeftCell.Value = .Value
        .Visible = False
        .Value = ""
    End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'whenever column 1 (A) is selected, _
    make the ComboBox visible _
    assign the ComboBox location and size to the active cell location and size _
    OTHERWISE make the ComboBox invisible
    If Target.Column = 1 Then
        With ComboBox1
            .Visible = True
            .Top = Target.Top
            .Left = Target.Left
            .Width = Target.Width
            .Height = Target.Height
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[DeLaMartre]

Great advice, Skip! I have always used the "Validation" technique for sake of expediency, but I can appreciate the merits of your way to do this.

I will certainly make use of your tip!

Thanks,

-Bob in California

 

[ray00]

I am a newbie in Excel. I needed a solution to this problem. I tried Skip's advice. It works but I am having trouble selecting the value in the cell. When I click on the value in combobox, it doesn't show up in the cell. Its just blank? what am I doing wrong here?

Thanks in advance,

 

[SkipVought]

ray00,
Send me an abbreviated copy of your workbook and I'll look at it. Skip,
SkipAndMary1017@mindspring.com

 

[ray00]

Skip:
Thank you very much for your help.
I have sent you the workbook at you email address. Let me know in case it didn't go through. My email is cxde2000@yahoo.com