[VBA] Check if shape is a picture

[pit84]

Hi

I've got quite a few excel files to redo, each with hundreds of shapes in them. Some of them are pictures, some of them are standard shapes like square, diamond etc. Pictures are a mess and I would like to resize all of them to same size (which they should be in the beginning)

Dim ws As Worksheet
Dim shp As Shape

For Each ws In ActiveWorkbook.workshets
For Each shp In ws.Shapes
shp.Width = 2
shp.Height = 2
Next shp
Next ws

it will change the size of all shapes, including standard ones. How do I limit it to pictures only?

Thanks for help

 

[ImranMalek]

Hi

Please try below code

Sub ResizePictures()    Dim Pic As Object
    For Each Pic In ActiveSheet.Pictures
        Pic.Width = 20
        Pic.Height = 20
    Next Pic
End Sub

Hope it helps.

 

[MickG]

Use Bits of this code to achieve what you want !!!

Dim Pic As Shape
For Each Pic In ActiveSheet.Shapes
      [B][COLOR=#ff0000]  If Pic.Type = msoPicture Then[/COLOR][/B]
           MsgBox Pic.Type & "/" & Pic.Name
        Else
            MsgBox Pic.Type & "/" & Pic.Name
        End If
Next Pic

 

[pit84]

Thank you, it did the trick

 

[Rick Rothstein]

On my system, the all pictures are placed with the "Lock aspect ratio" checkbox checked (not sure if that is universally true or not though), so changing only one of the properties (width or height) resizes the picture correctly. Given that, you can resize all of the pictures on the worksheet using a single line of code. For example, if you wanted to set all the picture widths to 200...

ActiveSheet.Pictures.Width = 200

If it turns out that your aspect ratio is not locked, or if you deliberately turn it off using this line of code...

ActiveSheet.Pictures.ShapeRange.LockAspectRatio = msoFalse

(if you wanted to turn the ration back on for all the pictures, use msoTrue) then you would need to set the Height property individually like this (assuming you wanted to set the height to, say, 150)...

ActiveSheet.Pictures.Height = 150