DropDown Object

[robertoguillen]

Do you have any experience with the DropDown Object?

I`m trying to create this control in cells but the code I got from my referece book doesn't work.

Doyou have any example that works?

Many thanks,

Roberto.

this is the code that is not working:


Sub AddDropDown(Target As Range)

Dim ddBox As DropDown

Dim vaProducts As Variant
Dim Sheet2 As Worksheet
Dim i As Integer

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


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


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

Private Sub EnterProdInfo()

Dim vaPrices As Variant

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

With Sheet2.DropDowns(Application.Caller)

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


End Sub

[Andy Pope]

Hi robertoguillen and welcome,

Are you sure you typed in the code exactly as given?

I found two major problems.
The Variable Sheet2 will override the builtin use of the reference to Sheet2. Having done that it will cause a probelm as you have not made a reference to any worksheet.

The other problem was that the OnAction name did not match the name of the macro it was suppose to run.

Sub Test() 
    AddDropDown Range("D4") 
End Sub 
Sub AddDropDown(Target As Range) 
    Dim ddBox As DropDown 
    Dim vaProducts As Variant 
     '    Dim Sheet2 As Worksheet  ' Commented out
    Dim i As Integer 
     
    vaProducts = Array("Water", "Oil", "Chemicals", "Gas") 
    Set ddBox = Sheet2.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height) 
    With ddBox 
        .OnAction = "EnterProductInfo" ' name corrected
        For i = LBound(vaProducts) To UBound(vaProducts) 
            .AddItem vaProducts(i) 
        Next i 
    End With 
End Sub 
 
Private Sub EnterProductInfo() 
    Dim vaPrices As Variant 
     
    vaPrices = Array(15, 12.5, 20, 18) 
    With Sheet2.DropDowns(Application.Caller) 
        .TopLeftCell.Value = .List(.ListIndex) 
        .TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1)) 
        .Delete 
    End With 
End Sub 

[robertoguillen]

Hi Andy,

THÀNKS A MILLION !!!

I Know realize what I was doing wrong.

CHEERS !!!

Roberto.