Place an autoshape

[alstubna]

Whithout going through a whole bunch of trial and error, how can I easily set
the co-ordinates to place an autoshape rectangle over specific cells E10:E11?

 

[Rick Rothstein \(MVP - VB\)]

This code will create the Rectangle Shape and place it on top of the Range
specified in the With statement on the worksheet indicated in the first line
of the With/EndWith block. The code also stores the name for the Rectangle
and its Index number in case you want to interact with it later on in your
code.

Dim RectangelIndexE10E11 As Long
Dim RectangleNameE10E11 As String
With Range("E10:E11")
Worksheets(1).Shapes.AddShape msoShapeRectangle, _
.Left, .Top, .Width, .Height
RectangelIndexE10E11 = Shapes.Count
RectangleNameE10E11 = Shapes(Shapes.Count).Name
End With

Rick

 

[alstubna]

Thanks Rick

That's certainly much neater than what I was trying to do.

BTW, I got a runtime error on this line:

RectangelIndexE10E11 = Shapes.Count

I didn't need that for my needs so I just took it out.

Al Stubna

 

[Rick Rothstein \(MVP - VB\)]

The error probably came from my running the code with the sheet with the
Shape on it active and, perhaps, you were not doing so. This should fix the
problem I would think...

Dim RectangelIndexE10E11 As Long
Dim RectangleNameE10E11 As String
With Range("E10:E11")
Worksheets(1).Shapes.AddShape msoShapeRectangle, _
.Left, .Top, .Width, .Height
End With
With Worksheets(1)
RectangelIndexE10E11 = .Shapes.Count
RectangleNameE10E11 = .Shapes(Shapes.Count).Name
End With

By the way, the actual sheet names could be subsituted for the sheet index
number that I used. For example,

Dim RectangelIndexE10E11 As Long
Dim RectangleNameE10E11 As String
With Range("E10:E11")
Worksheets("Sheet1").Shapes.AddShape msoShapeRectangle, _
.Left, .Top, .Width, .Height
End With
With Worksheets("Sheet1")
RectangelIndexE10E11 = .Shapes.Count
RectangleNameE10E11 = .Shapes(Shapes.Count).Name
End With

Rick


Thanks Rick

That's certainly much neater than what I was trying to do.

BTW, I got a runtime error on this line:

RectangelIndexE10E11 = Shapes.Count

I didn't need that for my needs so I just took it out.

Al Stubna