Add shape to range (.AddShape method)

Question

For Excel-2007:

Without using select, what is the best way to add a shape to a specific range or cell?

So far the best way I've found is by calculating using EntireColumn.Left and the like.

Can the AddShape method be used within a range to automatically create a shape within the range? Or must AddShape always locate a new shape relative to the upper-left corner of the document?

Maybe this could help [vba to add a shape at a specific cell location in Excel](http://stackoverflow.com/questions/16038893/vba-to-add-a-shape-at-a-specific-cell-location-in-excel) — Chris, Sep 13 '13 at 20:09

Gary's Student · Accepted Answer · 2016-08-23T16:56:45.173

Here is an example of placing a Shape (in this case a TextBox) on a worksheet without any Selection s or any references to the upper left-hand corner of the document, only the parameters of the range in question:

Sub CoverRange()
    Dim r As Range
    Dim L As Long, T As Long, W As Long, H As Long
    Set r = Range("A2:H8")
    L = r.Left
    T = r.Top
    W = r.Width
    H = r.Height
    With ActiveSheet.Shapes
        .AddTextbox(msoTextOrientationHorizontal, L, T, W, H).TextFrame.Characters.Text = "Test Box"
    End With
End Sub

score 2 · Answer 2 · edited May 23 '17 at 12:23

I marked @Gary's Student's answer as the best... but since I had trouble finding much info that related to what I was doing, I thought some code pasting here might help someone in the future.

The procedure @Gary's suggested can be adapted to cover a range of cells. I wanted to place a small shape on the right hand side of some cells in a range, that performed some functions on those cells. So, applying the .AddShape method:

Dim cl As Range, rg As Range
Set rg = Range("J2", Range("J2").End(xlDown))
For Each cl In rg
  With ActiveSheet.Shapes.AddShape(92, cl.Width - 10 + cl.Left, cl.Top + 5, 10, 10)
    .OnAction = "click_pm_update"
    .Name = cl.Row
    .Shadow.Visible = False
  End With
Next

This creates a small star to the right of each cell. The star's name reflects the row of that star, and when clicked it calls the "click_pm_update" procedure.

As a further note, click_pm_update uses the Application.Caller method, combined with the shape's name (which reflects the row the shape is in), to determine what cells to act on:

Private Sub click_pm_update()
Dim pmRow As String: pmRow = ActiveSheet.Shapes(Application.Caller).Name
'etc, etc

See here for some useful info on the Application.Caller method.

The beauty of this is that the spreadsheet can continue to be used as normal until the user clicks on the shape. This adds a lot of customization to the spreadsheet.

Add shape to range (.AddShape method)

2 Answers2