probably a quick and easy question but I cannot seem to figure it out. here
goes

I have a button that inserts a copy of a shape into the active cell when
clicked. what I want to do is delete the existing shape if one exists (in
that activecell) and paste in the new one. here is my code. thanks for any
help. much appreciated.

Private Sub CommandButton2_Click()
'check for existing shape in activecell - If one exists then delete, if not
continue
ActiveSheet.Shapes("Picture 1").Select
Selection.Copy
ActiveCell.Select
ActiveSheet.Paste
Selection.ShapeRange.IncrementTop 10
Selection.ShapeRange.IncrementLeft -2
ActiveCell.Value = 1
ActiveCell.Next.Select
End Sub

I'm not sure how to find the shape that is in a particular cell
directly. The approach I took is to look at all the shapes on the
sheet, then determine if they are in the active cell. This may not
work if you have too many shapes on the sheet. But if you only have a
few shapes, you can add this to the beginning of the subroutine you
listed above.

Dim shp As Shape, r As Range
For Each shp In ActiveSheet.Shapes
Set r = Intersect(ActiveCell, Range(shp.TopLeftCell,
shp.BottomRightCell))
If Not r Is Nothing Then
shp.Delete
End If
Next shp

thank you for the reply. I DO have a lot of shapes, and was trying to stay
away from looping through all of them. Suprisingly this is faster than I
expected and works great. Thanks!

If anyone knows of a way to do this without looping please share. thanks!

"jasontferrell" wrote:

> I'm not sure how to find the shape that is in a particular cell
> directly. The approach I took is to look at all the shapes on the
> sheet, then determine if they are in the active cell. This may not
> work if you have too many shapes on the sheet. But if you only have a
> few shapes, you can add this to the beginning of the subroutine you
> listed above.
>
> Dim shp As Shape, r As Range
> For Each shp In ActiveSheet.Shapes
> Set r = Intersect(ActiveCell, Range(shp.TopLeftCell,
> shp.BottomRightCell))
> If Not r Is Nothing Then
> shp.Delete
> End If
> Next shp
>