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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top