How do you find the DropDown object in the Object Browser. I couldn't find it. Has anybody tried the DropDown Event on Page 177 of Excel 2000 VBA Programmers Reference. I can't seem to get it to work.

Thanks in advance.

Are you getting error messages or is the drop down not appearing?

XUMUSKIEFAN
Wrox Moderator

Hi,

I'm also having troubles with this code. It doesn't run due to bugs. The debugger appears.

Do you have any clue?

Thanks,

Roberto.

Hi,

I just realized it was my mistake. I have just tried this code and it does work.

I changed the example a little bit to make sure international users can take advantage of it.

Cheers !

Sub AddDropDown(Target As Range)

Dim ddBox As DropDown

Dim vaProducts As Variant
Dim i As Integer

Dim streamSheet As Worksheet
Set streamSheet = Worksheets(2)

vaProducts = Array("Water", "Oil", "Chemicals", "Gas")


    Set ddBox = streamSheet.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height)


With ddBox
    .OnAction = "EnterProdinfo"
    For i = LBound(vaProducts) To UBound(vaProducts)
        .AddItem vaProducts(i)
    Next i
End With
End Sub

Private Sub EnterProdInfo()

Dim vaPrices As Variant
Dim streamSheet As Worksheet
Set streamSheet = Worksheets(2)

vaPrices = Array(15, 12.5, 20, 18)

    With streamSheet.DropDowns(Application.Caller)

        .TopLeftCell.Value = .List(.ListIndex)
        .TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1))
        .Delete
    End With


End Sub