deleting objects using VBA
 

LS,

I'm looking for VBA code with which I can delete shapes (or any othe
object like inserted pictures) when they are/were placed within a rang
of selected cells.

If this doesnot make my problem clear the following description migh
help.
I'd like to select a range of cells (say A3:G50) and be able to delet
any object that is present 'in' this range using VBA code.

Thanks in advance for your help.

Coen van der Weijde

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

deleting objects using VBA
 

Hi Coen van der Weijden,

Here's a subroutine that should do what you're looking for:

Sub RemoveObjectsFromSelection()
Dim ole As OLEObject
Dim shp As Shape

For Each ole In Selection.Parent.OLEObjects
If Not Application.Intersect(Selection, _
ole.TopLeftCell) Is Nothing Then
ole.Delete
End If
Next ole

For Each shp In Selection.Parent.Shapes
If Not Application.Intersect(Selection, _
shp.TopLeftCell) Is Nothing Then
shp.Delete
End If
Next shp
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


cweijden wrote:
LS,

I'm looking for VBA code with which I can delete shapes (or any other
object like inserted pictures) when they are/were placed within a
range of selected cells.

If this doesnot make my problem clear the following description might
help.
I'd like to select a range of cells (say A3:G50) and be able to delete
any object that is present 'in' this range using VBA code.

Thanks in advance for your help.

Coen van der Weijden


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/