Excel 2003 - Shapes

[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

 

[PBezucha]

Mike,
Search up (VBA->View->)ObjectBrowser for msoShapeType.

 

[Rob Bovey]

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.)

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

 

[Mike Faulkner]

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

 

[Mike Faulkner]

Rob

Many thanks.

Regards
Mike

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

 

[Rob Bovey]

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

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

 

[JLGWhiz]

If you use Shape.Name along with the type, it will give you triangle,
rectangle, etc.