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

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" <Ken@discussions.microsoft.com> wrote in message
news:708545E7-A06B-433D-9F56-B4B6C6551B8D@microsoft.com
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

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" <Ken@discussions.microsoft.com> wrote in message
> news:708545E7-A06B-433D-9F56-B4B6C6551B8D@microsoft.com
> 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
>

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" <Ken@discussions.microsoft.com> wrote in message
> > news:708545E7-A06B-433D-9F56-B4B6C6551B8D@microsoft.com
> > 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
> >