Shapes: identifying different types (charts, textboxes, pictures e

[David Macdonald]

Since I use various shapes quite a lot so I use a form. I get the list of the
shapes on a sheet very simply:
For Each Shape In ActiveSheet.Shapes
ListBox1.AddItem (Shape.name)
Next Shape
I have controls where I can set margins, add/modify alternative text and
shape names, convert Textboxes to Pictures etc.

Now the problem. My list contains all the TextBoxes, Charts, Rectangles,
Pictures and Ovals. And I noticed yesterday if I have any filters on, the
DropDowns are there too and create all sorts of trouble if I select them!
How can I filter the different types of Shape so my list will only show the
type I want? I'll use checkboxes I guess but what code can I use to recognise
a Picture from a chart or an activeX control from a yellow oval?

Thanks for any assistance.

 

[Peter T]

Dim shp As Shape
Dim shpType As MsoShapeType

'code

shpType = shp.Type
If shpType = msoFormControl Then
If TypeName(shp.DrawingObject) = "DropDown" Then
'probably a filter arrow
Else
' a Forms control
Elseif

There are (from memory) 17 shapeTypes, maybe use Select case to group
similar types.

Regards,
Peter T

 

[Joel]

the way I usally solve this problem is with the code below. The type will
tell you what the object is. YOu can also get the oleboject fro the shape.


Sub shapes()

For Each Shape In ActiveSheet.shapes
MsgBox (Shape.Name & " : " & Shape.Type)
If Shape.Type = msoOLEControlObject Then
Set obj = ActiveSheet.OLEObjects(Shape.Name)
End If
Next Shape

End Sub

 

[Jacob Skaria]

Check out the below link
http://www.mvps.org/dmcritchie/excel/shapes.htm

If this post helps click Yes

 

[Dave Peterson]

Ron de Bruin has lots of info (and code samples) here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm