Hi,
I have seen how you can delete all oleobjects from an active sheet. But I
cannot figure out how to delete an oleobject (embedded file object) from only
the current row or active cell.

For instance, in my worksheet, most rows in columns c have embedded file
objects. I just want a macro to delete the current row's embedded file
object, not all objects in the active sheet.

Is this possible?

Thanks so much,
Liz

Try something like the following:

Dim OleObj As OLEObject
For Each OleObj In Worksheets("Sheet1").OLEObjects
If OleObj.TopLeftCell.Row = ActiveCell.Row Then
OleObj.Delete
End If
Next OleObj

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sun, 28 Mar 2010 13:37:01 -0700, Liz
<(E-Mail Removed)> wrote:

>Hi,
>I have seen how you can delete all oleobjects from an active sheet. But I
>cannot figure out how to delete an oleobject (embedded file object) from only
>the current row or active cell.
>
>For instance, in my worksheet, most rows in columns c have embedded file
>objects. I just want a macro to delete the current row's embedded file
>object, not all objects in the active sheet.
>
>Is this possible?
>
>Thanks so much,
>Liz

Hi Liz,

Something like the following might help. You could test both Left and Top
position for an object in an individual cell.

Sub DeleteOleObjsInRow()
Dim oleOBJ As OLEObject
Dim topThisRow As Long
Dim topNextRow As Long

topThisRow = Rows(7).Top
topNextRow = Rows(8).Top

With ActiveSheet
For Each oleOBJ In .OLEObjects
With oleOBJ
If .Top >= topThisRow And .Top <= topNextRow Then
.Delete
End If
End With
Next oleOBJ
End With

End Sub

--
Regards,

OssieMac


"Liz" wrote:

> Hi,
> I have seen how you can delete all oleobjects from an active sheet. But I
> cannot figure out how to delete an oleobject (embedded file object) from only
> the current row or active cell.
>
> For instance, in my worksheet, most rows in columns c have embedded file
> objects. I just want a macro to delete the current row's embedded file
> object, not all objects in the active sheet.
>
> Is this possible?
>
> Thanks so much,
> Liz

Hi again Liz,

That was poor coding that I posted even though it will work because working
with the active sheet anyway. Assigning the .Top to the variables should have
been inside the With ActiveSheet then it is transportable to code that
specifies the sheet name instead of ActiveSheet.

Sub DeleteOleObjsInRow()
Dim oleOBJ As OLEObject
Dim topThisRow As Long
Dim topNextRow As Long

With ActiveSheet
topThisRow = .Rows(7).Top
topNextRow = .Rows(8).Top
For Each oleOBJ In .OLEObjects
With oleOBJ
If .Top >= topThisRow And .Top <= topNextRow Then
.Delete
End If
End With
Next oleOBJ
End With

End Sub


--
Regards,

OssieMac

Thanks so much for your help!!! I wish I had your skill levels!

"Liz" wrote:

> Hi,
> I have seen how you can delete all oleobjects from an active sheet. But I
> cannot figure out how to delete an oleobject (embedded file object) from only
> the current row or active cell.
>
> For instance, in my worksheet, most rows in columns c have embedded file
> objects. I just want a macro to delete the current row's embedded file
> object, not all objects in the active sheet.
>
> Is this possible?
>
> Thanks so much,
> Liz