deleting OLEObjects in a loop removes only odd numbered objects 2

[dixiematic]

Hello,
I have created OLEObjects by "OLEObjects.Add ClassType..".
I count them by "OLEObjecs.Count". Then I loop through all the objects on a worksheet and want to delete those with the name "Label", but only those Labels(j) are deleted, where j is an odd number. Error 1004 is then reported. What should I do do remove all Labels?
Dixiematic

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

Items in a collection are always shuffled down to fill the gaps when an item is deleted from the collection. This results in the type of behaviour you have observed.

So, when deleting start from the end of the collection and work backwards, i.e. instead of something like:

[blue]    Dim myObjects As Object
    Dim lp As Long
    
    Set myObjects = ActiveSheet.OLEObjects
    
    For lp = 1 To myObjects.Count
        myObjects(lp).Delete
    Next[/blue]

try

[blue]    Dim myObjects As Object
    Dim lp As Long
    
    Set myObjects = ActiveSheet.OLEObjects
    
    For lp = myObjects.Count To 1 Step -1
        myObjects(lp).Delete
    Next[/blue]

 

[macropod]

Alternatively:

Dim MyObject As Object
For Each MyObject In ActiveSheet.OLEObjects
  MyObject.Delete
Next

Cheers
[MS MVP - Word]

 

[dixiematic]

To PHV

My code to add controls is

Sub Add_ActiveX_ControlsAddlabel()
Dim MyOleObject As Object
Dim i As Integer
For i = 1 To 6
MyTop = i * 80
Worksheets("List1").OLEObjects.Add ClassType:="Forms.Label.1", Left:=300, Top:=MyTop, Height:=50, Width:=200
Next i
End Sub

and a piece to remove labels

Sub RemoveMyLabels()
Dim Myobject As Object
For j = 1 To Worksheets("List1").OLEObjects.Count
Set Myobject = ActiveSheet.OLEObjects(j)
If Left$(Myobject.Name, 5) = "Label" Then Myobject.Delete
Next j
End Sub

There are some other objects on a sheet which I do not want to remove. The reason for a cumbersome code is that I do not know how to count Labels only.
Dixiematic

 

[PHV]

So, I'd try this:

Sub RemoveMyLabels()
Dim Myobject As Object
With Worksheets("List1").OLEObjects
  For j = .Count To 1 Step -1
    Set Myobject = .Item(j)
    If Left$(Myobject.Name, 5) = "Label" Then Myobject.Delete
  Next j
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

And again:

Sub RemoveMyLabels()
Dim MyObject As Object
For Each MyObject In Worksheets("List1").OLEObjects
  If Left$(Myobject.Name, 5) = "Label" Then Myobject.Delete
Next
End Sub

Cheers
[MS MVP - Word]

 

[dixiematic]

To PHV and Macropod
thanks, both subrutines work fine.
Dixiematic

 

[strongm]

Remarkable ...

 

[SkipVought]

Hmmmmmmmm?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Double hmmmmmm? Although, I can guess....

Gerry

 

[SkipVought]

So that must mean...

Remarkable raised to the hmmmmmmmm-th power squared! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

No, it is remarkable<sup>m[/sup. I fail to see why it would be also be squared.

Oh wait...I see. "Double".

Fair enough, although I think it would be: 2remarkable (or even TOOremarkable).


I am confused.

Gerry</sup>

 

[SkipVought]

Just trying some extreme sophistry, which, by virtue of its 'extremity', might be rather loud.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}