Announcement

Collapse
No announcement yet.

DropDown Object

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • DropDown Object

    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

  • #2
    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.

    Code:
    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

    Cheers
    Andy

    Comment


    • #3
      Hi Andy,

      TH└NKS A MILLION !!!

      I Know realize what I was doing wrong.

      CHEERS !!!

      Roberto.

      Comment

      Working...
      X