How to select only pictures in activesheet

[ABabeNChrist]

I have worksheets with various types of shapes and I’d like to be able to use code to only select all pictures on activesheet.

[Rudi]

You can use this...

Code: Select all

Sub SelectAllPics()
    ActiveSheet.Pictures.Select
End Sub

[ABabeNChrist]

Thanks Rudi, but I tried that last night and it seem to only select command buttons

[HansV]

Please try again. ActiveSheet.Pictures, as the name indicates, is the collection of all pictures on the sheet, not of command buttons.

[ABabeNChrist]

here is a sample workbook that will explain

Book1.xlsm

[HansV]

I see. It selects ActiveX controls as well as pictures - not ONLY command buttons as you claimed.

One workaround would be to use Forms command buttons instead of ActiveX controls.
Another is to use the following code:

Code: Select all

Private Sub CommandButton33_Click()
    Dim shp As Shape
    Dim f As Boolean
    For Each shp In Me.Shapes
        Select Case shp.Type
            Case msoLinkedPicture, msoPicture
                shp.Select Replace:=Not f
                f = True
        End Select
    Next shp
End Sub

[ABabeNChrist]

I believe this is correct, so if I wish to place within a module and use on active sheet I would then replaced the Me with ActiveSheet

Code: Select all

    Dim shp As Shape
    Dim f As Boolean
    For Each shp In ActiveSheet.Shapes
        Select Case shp.Type
            Case msoLinkedPicture, msoPicture
                shp.Select Replace:=Not f
                f = True
        End Select
    Next shp

[Rudi]

Looking into this again, the line does actually select Active-X controls (all types).
That is unusual!

The form controls are not selected though.
Can you by any chance change the Active-X controls to form controls?

[HansV]

I believe this is correct, so if I wish to place within a module and use on active sheet I would then replaced the Me with ActiveSheet

Yes, that is correct.

[Rudi]

Hans,

Why use the Boolean variable?
It seems to work with:
shp.Select Replace:=False

[ABabeNChrist]

Thank you Hans, with the code you provided it seems to work perfectly now.

[HansV]

@Rudi: If a shape had already been selected before running the code, it would be included in the selection.
The Boolean variable is used to ensure that the first shape is not added to the selection, but is selected by itself.

[Rudi]

TX. That clarifies it

[ABabeNChrist]

I’m using this code to select all photos on activesheet and it works great. But when trying to compress multiple photos in excel 2010 or 2013 it doesn’t seem to give an option for selecting more than a single photo (apply only to this picture) as with 2007 it gives you an option to apply to selected pictures only. Any suggestions.

Code: Select all

Private Sub CommandButton33_Click()
    Dim shp As Shape
    Dim f As Boolean
    For Each shp In Me.Shapes
        Select Case shp.Type
            Case msoLinkedPicture, msoPicture
                shp.Select Replace:=Not f
                f = True
        End Select
    Next shp
End Sub

[HansV]

When I click the button to select ALL pictures, this is the dialog I see when I click Compress Pictures in Excel 2010:

S0737.png

And this is the version I get when I select only a single picture, then click Compress Picture:

S0738.png

[ABabeNChrist]

Thank you Hans I only have 2007 and most of my other experiences are either thru internet search or other users. For some reason it does seem to compress as nicely in 2010/2013 as with 2007 or at least that is my experience.

Are 2010 and 2013 similar with compressing pictures

[HansV]

I don't have 2013, but I assume it's similar to 2010.

[Rudi]

2013 is the same as 2010.

[ABabeNChrist]

Thank you Rudi
I’m wondering what is the equal to “Apply to selected pictures only” as with 2007, when selecting multiple pictures.
It seems that “Selected Pictures” option is greyed out in 2010/2013

[HansV]

I did some testing in Excel 2010.

S0739.png

It turns out that the label "Apply only to this picture" is slightly misleading - it should actually be "Apply only to the selected picture(s)": if you have selected several pictures, and if the "Apply only to this picture" check box is ticked, all selected pictures will be compressed. If you clear the check box, all pictures in the entire workbook will be compressed.