Deleting Shapes from an Active Cell then clear the contents ??

**hans302** · 01-03-2017, 05:48 AM

Hi I have a sheet that contains shapes in a number of cells (sometimes more than one shape per cell)

I need VBA code to clear all the shapes from 'ONLY' the active cell and then clear the contents of that active cell.

The code I am trying is below - But get a debug error on "For Each Shp In ActiveCell.Shapes"

Not sure what I am doing wrong?

Sub DeleteShapes_ActiveCell()

Dim Shp As Shape

For Each Shp In ActiveCell.Shapes
If Shp.ActiveCell = ActiveCell Then Shp.Delete
ActiveCell = ""
Next Shp
ActiveCell.ClearContents
Range("c3").Select

End Sub

**meabrams** · 01-03-2017, 06:19 AM

I think this will do what you are asking

Sub DeleteShapes_ActiveCell()

 Dim Shp As Shape

 For Each Shp In ActiveSheet.Shapes
 If Not Intersect(Application.ActiveCell, Shp.TopLeftCell) Is Nothing And _
    Not Intersect(Application.ActiveCell, Shp.BottomRightCell) Is Nothing Then
    Shp.Delete
    ActiveCell = ""
End If
 Next Shp
 ActiveCell.ClearContents
 Range("c3").Select

 End Sub

And for future posting please make sure to use the code tags around your macro ( it’s the thing on the posting tool bar that looks like this # )

**hans302** · 01-03-2017, 06:25 AM

Thanks a lot !!!!

Deleting Shapes from an Active Cell then clear the contents ??

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Deleting Shapes from an Active Cell then clear the contents ??

Re: Deleting Shapes from an Active Cell then clear the contents ??

Re: Deleting Shapes from an Active Cell then clear the contents ??

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Needing VBA code to clear contents of cell when the contents of another cell is cleared

[SOLVED] Moving shapes to the middle left of an active cell

Clear merged cell contents in comand button(clear all)

[SOLVED] vba to clear contents in specific ranges on active sheet

[SOLVED] Clear contents based on contents of another cell

Macro is deleting sheet, and I would like it to clear contents

How to set the position of a active cell and clear the contents of the cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions