Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Relating Top,Left, height, and Width (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    I need to place DropDown controls over about 31 different cells.

    I know that the columns where these DropDowns will be placed are 15, and the row heights are 65.

    Most of the DropDowns will be added at adjacent cells, but some I will need to offset a bit.

    Is there a way to relate the Top, Left, Height and Width to the measurements of the cell, and then offset by what measurements I need to get to the next cell? This will be done by code BTW, so I am not sure if screen resolution will matter, thus I am thinking about the cells as anchors.

    TIA

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Relating Top,Left, height, and Width (Excel 97 and >)

    Why not base it on the left, top, width and height of the cell of interest?

    This will add a combobox over E12 and it will be the size of E12.
    Change as appropriate.
    Steve

    <pre>Option Explicit
    Sub Macro1()
    Dim l As Long
    Dim t As Long
    Dim h As Long
    Dim w As Long
    Dim rng As Range
    Dim cbo As OLEObject

    Set rng = Worksheets(1).Range("e12")

    l = rng.Left
    w = rng.Width
    h = rng.Height
    t = rng.Top

    Set cbo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.1", Link:=False, _
    DisplayAsIcon:=False, Left:=l, Top:=t, Width:=w, Height:=h)
    End Sub</pre>


  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Relating Top,Left, height, and Width (Excel 97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    As to your question: <<< Why not base it on the left, top, width and height of the cell of interest? >>>

    Some times I think I explained what I want to accomplish pretty well, and other times I am sure I missed the point.

    Between these two states, I guess I get my questions answered.

    Some times, there is just not enough words to explain what goes on in the gray matter <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>

    But that is exactly what I meant and you got it right for me.

    Also thanks for the code sample. I now need to copy and paste, change some things around and be done.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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