Positioning an Object (say a box) on a Excel cell

[Guest]

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

 

[Jim Cone]

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


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

 

[Guest]

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

 

[Guest]

Figured it out. . . Right click on the rectangle and select the properties
tab. Several useful selections.

Sorry,

Christmas