Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: DropDown Object

  1. #1
    Join Date
    10th July 2004
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313
    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.

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


  3. #3
    Join Date
    10th July 2004
    Posts
    2
    Hi Andy,

    TH└NKS A MILLION !!!

    I Know realize what I was doing wrong.

    CHEERS !!!

    Roberto.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Yes No Dropdown
    By guerilla in forum EXCEL HELP
    Replies: 4
    Last Post: December 13th, 2006, 21:56
  2. Replies: 14
    Last Post: September 30th, 2006, 23:45
  3. Gettting all the object from the object browser
    By hgus393 in forum EXCEL HELP
    Replies: 2
    Last Post: March 31st, 2006, 15:30
  4. Replies: 1
    Last Post: January 21st, 2005, 06:13
  5. Replies: 2
    Last Post: May 7th, 2004, 17:42

Bookmarks

Posting Permissions

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