Excel 2003 - Shapes

  • Thread starter Thread starter Mike Faulkner
  • Start date Start date
M

Mike Faulkner

App: Excel 2002
OS: WinXP

Looping through the Shapes collection in a Worksheet I get three different
Types returned (i.e. 13, 17 & 1) . How can I then identify what Type of Shape
these are (i.e, Texbox, Picture, Comment etc.)

Many thanks in advance

Regards
Mike
 
Mike Faulkner said:
App: Excel 2002
OS: WinXP

Looping through the Shapes collection in a Worksheet I get three different
Types returned (i.e. 13, 17 & 1) . How can I then identify what Type of
Shape
these are (i.e, Texbox, Picture, Comment etc.)
Click to expand...

Hi Mike,

The Shape.Type property returns the value of one of the msoShapeType
enumeration members. You can view these members using the Object Browser
(press F2 in the Visual Basic Editor). There's no quick way to look up a
specific number, you just have to select msoShapeType from the list on the
left and then select each enumeration member from the list on the right and
see if its value corresponds to the one you're looking for. For the ones you
mention:

1 = msoAutoShape
13 = msoPicture
17 = msoTextBox

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm
 
Hello

Many thanks for the replies.

I want to loop through the msoShapeType collection extracting the type of
Shape (i.e. 17 = Textbox, 13=Picture etc.)

Regards
Mike
 
Rob

Many thanks.

Regards
Mike

Rob Bovey said:
Hi Mike,

The Shape.Type property returns the value of one of the msoShapeType
enumeration members. You can view these members using the Object Browser
(press F2 in the Visual Basic Editor). There's no quick way to look up a
specific number, you just have to select msoShapeType from the list on the
left and then select each enumeration member from the list on the right and
see if its value corresponds to the one you're looking for. For the ones you
mention:

1 = msoAutoShape
13 = msoPicture
17 = msoTextBox

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm
Click to expand...
 
Mike Faulkner said:
I want to loop through the msoShapeType collection extracting the type of
Shape (i.e. 17 = Textbox, 13=Picture etc.)
Click to expand...

Hi Mike,

While there's no built-in way to loop the msoShapeType enumeration
members, you can create something similar with an If...Then or Select Case
block in your code. Here's an example using Select Case:

Sub ShapeType()

Dim objShape As Shape

For Each objShape In ActiveSheet.Shapes
Select Case objShape.Type
Case 1 ''' AutoShape
''' Do something here
Case 13 ''' Picture
''' Do something here
Case 17 ''' TextBox
''' Do something here
Case Else
''' None of the above.
End Select
Next objShape

End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm
 
If you use Shape.Name along with the type, it will give you triangle,
rectangle, etc.
 
Back
Top