View Full Version : [SOLVED:] On Error GoTo ???
Mister_joe
01-31-2014, 04:32 AM
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.
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
01-31-2014, 05:54 AM
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
01-31-2014, 07:22 AM
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
01-31-2014, 07:31 AM
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
01-31-2014, 07:42 AM
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
01-31-2014, 08:42 AM
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
01-31-2014, 09:36 AM
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
01-31-2014, 02:42 PM
Thank you so very much. Your solution is exactly what I am looking for.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.