Delete all pictures from worksheet but not Form Controls

[dimitri]

Hi,
I'm looking for a bit of code that will allow me to delete all the pictures on a given worksheet without deleting the command buttons. Thanks.

 

[Rick Rothstein]

Give this macro a try...

Sub ClearPicturesOnly()
  Dim SH As Shape
  For Each SH In ActiveSheet.Shapes
    If SH.Type = msoPicture Then SH.Delete
  Next
End Sub

 

[dimitri]

Exactly what I needed. Thank you!

 

[shg]

Another way:

activesheet.pictures.delete

 

[Rick Rothstein]

Another way:

activesheet.pictures.delete

????

Okay, I see that statement works, but why? Pictures does not show up in VB's intellisense for a worksheet. Admittedly I only did a quickish search, but I can find no reference to a Pictures collection (or object for that matter, although objects do not usually take an "s" on the end of their names) anywhere within VB's help files (XL2003). Where is Pictures documented at? Or is this simply an undocumented collection that someone once tripped across?

 

[shg]

Right-click in an object browser window and select View hidden members to see lots of other collections -- Buttons, Drawings, DropDowns, GroupBoxes, Labels, ...

 

[Rick Rothstein]

Right-click in an object browser window and select View hidden members to see lots of other collections -- Buttons, Drawings, DropDowns, GroupBoxes, Labels, ...

All these years (both during my Excel time and my time before in the compiled VB world) and I never really played much with the Object Browser. Thanks for opening my eyes. Oh, and a side effect that I just noticed... setting the Show Hidden Members option in the Object Browser window makes the hidden members now show up in intellisense for the objects to which they apply.