On Error GoTo ???

[Mister_joe]

Guys,
I placed one Image control and two CommandButton controls on a worksheet. The code shown here ought to be simple enough, right?

Sub Macro1()    
    Dim obj As OLEObject
    Dim img As Image
    
    For Each obj In ActiveSheet.OLEObjects
    On Error GoTo Label2
        Set img = obj.Object
        Debug.Print obj.Name
Label2:
    Set img = Nothing
    Next
End Sub

The code runs for the Image control and for the first CommandButton control, but fails for the second CommandButton control. Does anyone know what is going on here?
Thanks.

[GTO]

Greetings Joe,

I'm about to hit the hay, but for any "answerer", it is a lot easier if you would attach the workbook with the code and oleobjects. That way we don't need to guess at re-creating what we imagine you are looking at. Hope that makes sense :-)

Mark

[Mister_joe]

Here's the macro I used to place the controls on the worksheet.

Sub Macro1()    
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Image.1", Link:=False, DisplayAsIcon:=False, Left:=162, Top:=96, Width:=175.5, Height:=87).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=439.5, Top:=103.5, Width:=153, Height:= 37.5).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=432, Top:=165.75, Width:=159.75, Height:=33.75).Select
End Sub

[anandbohra]

Try This

Sub Macro1()
    Dim obj As OLEObject
    Dim img As Image
     
    For Each obj In ActiveSheet.OLEObjects
        On Error GoTo Label2
        Set img = obj.Object
        Debug.Print obj.Name
    Next
            Exit Sub
Label2:
        Set img = Nothing

End Sub

[Mister_joe]

Thanks, but this does not allow the entire collection to be iterated. The moment an error occurs, the loop is exited and the execution ends. This is not exactly what I want. I want the entire collection to be iterated, but when an error is encountered, the looping should go to the next step.

[Mister_joe]

I notice that when the code encounters error and halts, debugging and hovering the mouse on obj.Object shows a ToolTip that has "obj.Object = False". Obviously, False cannot be assigned to a variable of type Image. But why is the second CommandButton returning False?

[Mister_joe]

People,
Who is really the culprit here, the "On Error GoTo Label2" or the "Set img = obj.Object" ? When the code execution encounters a error, execution ought to jump to the line labeled Label2. The "Set img = obj.Object" detects the type mismatch, but the execution does not jump to the line identified by Label2. Is it that VBA does not regard the error as error?

[Bob Phillips]

If you get an error condition, you should clear it before carrying on

Sub Macro1()
    Dim obj As OLEObject
    Dim img As Image
     
    For Each obj In ActiveSheet.OLEObjects
        On Error GoTo Label2
        Set img = obj.Object
        Debug.Print obj.Name
Label2:
        Set img = Nothing
        Resume Label3
Label3:
    Next
End Sub

But that is a very poor coding practice to get into in my view, might work here, but it will bite you in more complex code. Far better to handle the error at point, such as

Sub Macro1()
    Dim obj As OLEObject
    Dim img As Object
     
    For Each obj In ActiveSheet.OLEObjects
        Set img = obj.Object
        
        If TypeName(obj.Object) = "Image" Then
        
            Debug.Print obj.Name
        End If
        Set img = Nothing
    Next
End Sub

[Mister_joe]

Thank you so very much. Your solution is exactly what I am looking for.