Delete pic's and logos

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Hi

I have some pictures/logos at a spreadsheet, and I want to delete them. I recorded the following macro but when I run the macro it gives me an error it points to the third line of the code, can any one tell me what I'm doing wrong?

Sub mhiDeletePic()
'
' mhiDeletePic Macro
' Macro recorded 12/1/2005 by CONMHI
'

'
Sheets("Staffing Summary").Select
Cells.Select
ActiveSheet.Shapes("Picture 1").Select
Selection.delete
End Sub
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
I didn't test this guy's code. But he's on the right track. Try doing a search here. I know I've seen code that does this. Basically you loop through the shapes (or oleobjects) collection for the sheet, test to make sure the object is of the type you want to delete and then just delete it.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,670
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
This works for me.
Code:
Sub DeleteAllPics() 
Dim shp As Shape 
For Each shp In ActiveSheet.Shapes 
If shp.Type = msoPicture Then shp.Delete 
Next 
End Sub
HTH
Good Luck
John
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Highndry, that is essentially the code that was on the link that I referenced. However, unlike the OP in that other thread; John did have the good sense to use a variable name that was not a keyword.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,670
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Greg/Highndry.
I appreciate the faith you have in the macro but I can't take credit for it. It is, as far as I remember, supplied by Tom Urtis.
Thanks and regards.
John
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Thank you both for your help, however it doesn't delete the logo. I'm not sure why? May be I need to put a file name instead of "msoPicture"? What do you guys think.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

Just edit the code to tell you what type of shape your logo is.

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> PrintShapesType()
    <SPAN style="color:#00007F">Dim</SPAN> shp <SPAN style="color:#00007F">As</SPAN> Shape
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> shp <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Shapes
        Debug.Print shp.Name, shp.Type
    <SPAN style="color:#00007F">Next</SPAN> shp
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

Then look up the enumeration group msoShapeType in the object browser in the VBE and add edit your IF statement to look for that too (assuming only your logo is of the type you want to delete and that your logo is, indeed, a shape residing on the WS's drawing layer).
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Hi Greg

Is it possible to find out the name (file name) of the logo. Using right click gives you properties but not the file name of the pic/gif?
 

highndry

Board Regular
Joined
Nov 28, 2005
Messages
247
Ok slightly different approach, since that one wasn't working, from the following code:

Sheets("Staffing Summary").Select
Cells.Select
ActiveSheet.Shapes("Picture 14").Select
Selection.delete

instead of deleting picture 14, how can I delete Pinture 1 thru Picture infinity, or in other words all pictures.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,094
Members
412,441
Latest member
kelethymos
Top