searching for objects within the sheet

[nitind]

Is there a way to know whether there are objects (such as picture,pdf file,etc)inserted in a sheet and of what type it is ....?

 

[Jon von der Heyden]

Hello Nitin

Something like this perhaps:

Sub WhatShape()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
MsgBox Shp.Type
Next Shp

End Sub


This will return a number identifying the shape type. I believe the following table represents each type.

Excel Workbook
	M	N
6	1	msoAutoShape
7	2	msoCallout
8	3	msoCanvas
9	4	msoChart
10	5	msoComment
11	6	msoDiagram
12	7	msoEmbeddedOLEObject
13	8	msoFormControl
14	9	msoFreeform
15	10	msoGroup
16	11	msoLine
17	12	msoLinkedOLEObject
18	13	msoLinkedPicture
19	14	msoMedia Can not be used with this property. This constant is used with shapes in other Microsoft Office applications.
20	15	msoOLEControlObject
21	16	msoPicture
22	17	msoPlaceholder Can not be used with this property. This constant is used with shapes in other Microsoft Office applications.
23	18	msoScriptAnchor
24	19	msoShapeTypeMixed
25	20	msoTable
26	21	msoTextBox
27	23	msoTextEffect
Index

 

[nitind]

Hello Nitin

Something like this perhaps:

Sub WhatShape()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
MsgBox Shp.Type
Next Shp

End Sub


This will return a number identifying the shape type. I believe the following table represents each type.


Index

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Siemens Sans,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 69px"><COL style="WIDTH: 783px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1</TD><TD>msoAutoShape</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">2</TD><TD>msoCallout</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">3</TD><TD>msoCanvas</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">4</TD><TD>msoChart</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">5</TD><TD>msoComment</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">6</TD><TD>msoDiagram</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">7</TD><TD>msoEmbeddedOLEObject</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">8</TD><TD>msoFormControl</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">9</TD><TD>msoFreeform</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">10</TD><TD>msoGroup</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">11</TD><TD>msoLine</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">12</TD><TD>msoLinkedOLEObject</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">13</TD><TD>msoLinkedPicture</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">14</TD><TD>msoMedia Can not be used with this property. This constant is used with shapes in other Microsoft Office applications.</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">15</TD><TD>msoOLEControlObject</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">16</TD><TD>msoPicture</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">17</TD><TD>msoPlaceholder Can not be used with this property. This constant is used with shapes in other Microsoft Office applications.</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">18</TD><TD>msoScriptAnchor</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">19</TD><TD>msoShapeTypeMixed</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">20</TD><TD>msoTable</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">21</TD><TD>msoTextBox</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">23</TD><TD>msoTextEffect</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Thanks a lot JON