PDA

View Full Version : [SOLVED] How to check if a shape Exists?



pedrovarela
09-29-2008, 11:04 AM
I am trying to check if a certain shape exists but I always get an error (object doesn't support this Method/Property...). Any hint how should I use the following sentence:


If ActiveSheet.Shapes.Exists(Range("actReg").Value) Then

where

Range("actReg").Value is a well defined register with the name of a shape.
Thanks!!!!!!

Kenneth Hobs
09-29-2008, 12:02 PM
If you view the methods and properties for Shapes, you will see that Exists does not exist. Press F1 next to Shapes to see what Properties and Methods that it does support.

You need to iterate through the collection.
e.g.

Sub myShapes()
Dim ashp As Shape
For Each ashp In ActiveSheet.Shapes
Debug.Print ashp.Name
If ashp.Name = "AutoShape 1" Then
MsgBox "AutoShape 1 Exists"
Exit Sub
End If
Next ashp
End Sub

fb7894
09-29-2008, 01:50 PM
If you have many shapes on your worksheet, you could use the On Error to prevent the code from looping through all the shapes....


Sub Shape_Exists()
On Error GoTo Handler
Debug.Print ActiveSheet.Shapes("AutoShape 1").Name
Exit Sub
Handler:
MsgBox "Shape Does Not Exit"
End Sub

pedrovarela
09-30-2008, 01:09 AM
Thanks for the help! In my case the second proposition would be better since I have many shapes and I cannnot loop through all of them.

I have tried the second solutions but I still get an error message:" Run-time error...The item with the specified name wasn't found". Any hint?




For j = 1 To 6000
If ((Range("Model!C" & j).Value) = 0) Then
Range("actReg").Value = Range("Model!A" & j).Value
On Error GoTo Handler
ActiveSheet.Shapes(Range("actReg").Value).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Handler:
End If
Next j

pedrovarela
09-30-2008, 03:24 AM
Sorry, I properly used the second solution and works well. Thanks!!!!!!!

Kenneth Hobs
09-30-2008, 05:08 AM
If you are interested in optimum speed, they you should probably turn off screen updating and autocalculation and not use Select.

ActiveSheet.Shapes(Range("actReg").Value).ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)