Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positioning an Object (say a box) on a Excel cell
Using Visual Basic how can I position an object relative to the current cell
I am on. I don't know how to find the (points) position of the top corner of this cell to the top left hand corner of the worksheet. Can someone supply sample code -- Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positioning an Object (say a box) on a Excel cell
Ken,
Sub PutItInPlace() Dim rngPlace As Excel.Range Set rngPlace = ActiveSheet.Range("B5") With rngPlace ActiveSheet.Shapes("Rectangle 1").Top = .Offset(3, 4).Top ActiveSheet.Shapes("Rectangle 1").Left = .Offset(3, 4).Left End With Set rngPlace = Nothing End Sub End Sub Jim Cone San Francisco, USA "Ken" wrote in message Using Visual Basic how can I position an object relative to the current cell I am on. I don't know how to find the (points) position of the top corner of this cell to the top left hand corner of the worksheet. Can someone supply sample code -- Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positioning an Object (say a box) on a Excel cell
It may be worth noting that a shape such as a rectangle
has atleast the following properties: ..Top ..Left ..Height ..Width and does not have the properties: ..Bottom ..Right It may also be worth noting that the zeroed quantites int he .offset propery don't really matter. Example: It doesn't matter if your setting the top of the rectangle to the top of B5 or Z5. It will still align with the top of row 5. I've taken the previously posted code and made it a little more useful by also aligning the bottom and right sides of the shape. This particular example aligns it with the boundaries of a single cell. Does anyone know how to make this update automatically with the resizing of rows and columns? Christmas May Sub PutItInPlace() Dim rngPlace As Excel.Range Set rngPlace = ActiveSheet.Range("B5") With rngPlace 'ActiveSheet.Shapes("Rectangle 1").Top = .Offset(2, 0).Top 'ActiveSheet.Shapes("Rectangle 1").Left = .Offset(0, 2).Left ActiveSheet.Shapes("Rectangle 1").Top = .Offset(3, 0).Top ActiveSheet.Shapes("Rectangle 1").Left = .Offset(0, 4).Left ActiveSheet.Shapes("Rectangle 1").Height = .Offset(3, 4).Height ActiveSheet.Shapes("Rectangle 1").Width = .Offset(3, 4).Width End With Set rngPlace = Nothing End Sub "Jim Cone" wrote: Ken, Sub PutItInPlace() Dim rngPlace As Excel.Range Set rngPlace = ActiveSheet.Range("B5") With rngPlace ActiveSheet.Shapes("Rectangle 1").Top = .Offset(3, 4).Top ActiveSheet.Shapes("Rectangle 1").Left = .Offset(3, 4).Left End With Set rngPlace = Nothing End Sub End Sub Jim Cone San Francisco, USA "Ken" wrote in message Using Visual Basic how can I position an object relative to the current cell I am on. I don't know how to find the (points) position of the top corner of this cell to the top left hand corner of the worksheet. Can someone supply sample code -- Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positioning an Object (say a box) on a Excel cell
Figured it out. . . Right click on the rectangle and select the properties
tab. Several useful selections. Sorry, Christmas "Christmas May" wrote: It may be worth noting that a shape such as a rectangle has atleast the following properties: .Top .Left .Height .Width and does not have the properties: .Bottom .Right It may also be worth noting that the zeroed quantites int he .offset propery don't really matter. Example: It doesn't matter if your setting the top of the rectangle to the top of B5 or Z5. It will still align with the top of row 5. I've taken the previously posted code and made it a little more useful by also aligning the bottom and right sides of the shape. This particular example aligns it with the boundaries of a single cell. Does anyone know how to make this update automatically with the resizing of rows and columns? Christmas May Sub PutItInPlace() Dim rngPlace As Excel.Range Set rngPlace = ActiveSheet.Range("B5") With rngPlace 'ActiveSheet.Shapes("Rectangle 1").Top = .Offset(2, 0).Top 'ActiveSheet.Shapes("Rectangle 1").Left = .Offset(0, 2).Left ActiveSheet.Shapes("Rectangle 1").Top = .Offset(3, 0).Top ActiveSheet.Shapes("Rectangle 1").Left = .Offset(0, 4).Left ActiveSheet.Shapes("Rectangle 1").Height = .Offset(3, 4).Height ActiveSheet.Shapes("Rectangle 1").Width = .Offset(3, 4).Width End With Set rngPlace = Nothing End Sub "Jim Cone" wrote: Ken, Sub PutItInPlace() Dim rngPlace As Excel.Range Set rngPlace = ActiveSheet.Range("B5") With rngPlace ActiveSheet.Shapes("Rectangle 1").Top = .Offset(3, 4).Top ActiveSheet.Shapes("Rectangle 1").Left = .Offset(3, 4).Left End With Set rngPlace = Nothing End Sub End Sub Jim Cone San Francisco, USA "Ken" wrote in message Using Visual Basic how can I position an object relative to the current cell I am on. I don't know how to find the (points) position of the top corner of this cell to the top left hand corner of the worksheet. Can someone supply sample code -- Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comments positioning relative to cell not worksheet home | Excel Discussion (Misc queries) | |||
What is the deal with Comment Object Positioning? | Excel Discussion (Misc queries) | |||
Positioning a cell at the top left of the screen | Excel Programming | |||
vlookup to return the cell positioning where the criteria is found | Excel Programming | |||
Positioning a graphical object | Excel Programming |