Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: Put a Drop box in the Right Place

    I have the Code

    Code:
    Sub BuildDropDowns(cell)
            Dim Drops As Dropdown
            App_Sheet.Range(cell).Select
            Set Drops = App_Sheet.DropDowns.Add(540, 247.5, 103.5, 15.75)
            Drops.ListFillRange = "$S$2:$S$9"
            Drops.LinkedCell = cell
            Drops.DropDownLines = 8
            Drops.Display3DShading = False
            Set Drops = Nothing
    End Sub
    I Would like to do is move the DropDown so it sits on of the selected cell each time I pass a cell ref

    eg
    call BuildDropDowns("D17")
    call BuildDropDowns("D19")
    call BuildDropDowns("D21")
    call BuildDropDowns("D23")

    can someone point to the right place

    I now .Add(540, 247.5, 103.5, 15.75) = left,top,width,Height

    left,width,Height won't move
    Top the has be stump.
    Where is top ?? and how far is D17 form the top
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Have fix it

    though idd show you guys and guyess

    by
    Code:
    Sub BuildDropDowns(cell)
            App_Sheet.Range(cell).Select
            Dim Rang As Range
            Set Rang = App_Sheet.Range(cell)
            Dim Drops As Dropdown
            Set Drops = App_Sheet.DropDowns.Add(540, Rang.Top, 103.5, 15.75)
            Drops.ListFillRange = "$S$2:$S$9"
            Drops.LinkedCell = cell
            Drops.DropDownLines = 8
            Drops.Display3DShading = False
            Set Drops = Nothing
            Set Rang = Nothing
    End Sub
    remember reading some where about getting the height of a range
    well bugger me it works
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    okay i know youve solved this but i had a play about as well as i have never actually used drop downs.
    Here is my attempt

    Code:
    Sub testDropdowns()
        Dim rngAdd As Range
        Dim rngList As Range
        Dim wksadd As Worksheet
        
        Set rngAdd = Cells(2, 4) ' Range("D1")
        Set rngList = Range(Cells(1, 1), Cells(8, 1))
        Set wksadd = Worksheets("Sheet1")
        
        Call subAddDropDown(wksadd, rngAdd, rngList)
    
        Set rngAdd = Nothing
        Set rngList = Nothing
    End Sub
    
    Sub subAddDropDown(wksadd As Worksheet, rngAdd As Range, rngList As Range)
        Dim dp As DropDown
        'add a dropdown to the cell rngAdd
        Set dp = wksadd.DropDowns.Add(rngAdd.Left, rngAdd.Top, _
                                            rngAdd.Width, rngAdd.Height)
        'set the dropdown properties
        With dp
            .ListFillRange = rngList.Address
            .LinkedCell = rngAdd
            .DropDownLines = rngList.Rows.Count
        End With
            
        Set dp = Nothing
    End Sub
    the only real difference is that i am calling the function with a worksheet and range variables. I am using this range variable to determine the cell dimensions. Take from this what you like.

    All the best
    David

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Dave I like it Thanks for Your input
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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