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

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.
--
WinXP - Office2003 (Italian)

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

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

"David Macdonald" wrote in
message ...
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.
--
WinXP - Office2003 (Italian)

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

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

"David Macdonald" wrote:

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.
--
WinXP - Office2003 (Italian)

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

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

If this post helps click Yes
---------------
Jacob Skaria


"David Macdonald" wrote:

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.
--
WinXP - Office2003 (Italian)

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

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



David Macdonald wrote:

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.
--
WinXP - Office2003 (Italian)

--

Dave Peterson