Resize an image to fit cell dimensions

[Roxhan]

Hello friends,

I've created a macro button that will change the inserted image properties to 'Move and size with cells' and to adjust the size to fit within the cell. I found this code online and adjusted the height for my row height...

    Dim xPic As Picture
    For Each xPic In ActiveSheet.Pictures
        xPic.Placement = xlMoveAndSize
        xPic.ShapeRange.LockAspectRatio = msoTrue
        xPic.ShapeRange.Height = 85
    Next

This works well, however I need help to make two code adjustments:

1. Change from 'For each xPic in ActiveSheet' to only run on the image that is currently selected. I don't want it to resize all images that are in the active sheet, each time the button is pushed.
2. Resize the image depending on the aspect ratio, so that it will always fit within the cells dimensions (Desired cell dimensions are Height =85, Width = 230)
   • eg 1. Image A is 125h x 90w (ie Portrait aspect ratio)
     • As it's portrait, I want the macro to resize the image so the height is 85, and the width adjusts accordingly, maintaining aspect ratio
   • eg 2. Image B is 50h x 300w (ie Landscape aspect ratio)
     • As it's landscape, I want the macro to resize image so the width is 230, and the height adjusts accordingly, maintaining aspect ratio

Any help would be greatly appreciated,
Many thanks

 

[p45cal]

try something along the lines of:

Sub blah()
If TypeName(Selection) = "Picture" Then
  Aspect = 85 / 230
  With Selection
    .Placement = xlMoveAndSize
    .ShapeRange.LockAspectRatio = msoTrue
    PicAspect = .Height / .Width
    If Aspect > PicAspect Then
      .Width = 230
    Else
      .Height = 85
    End If
  End With
End If
End Sub

 

[Roxhan]

Thank you so much p45cal!

That seems to be working perfectly, so so great, really appreciate your help!

May I ask a quick follow up (as I'm trying to learn VBA as I go...)
Is PicAspect an excel 'set term'? I thought you had 'defined' Aspect at the top, but not defined PicAspect, so wasn't sure how excel understood how to read that.
May not be a quick answer, I'll try and find more details online.

Again - many thanks p45cal!

 

[p45cal]

PicAspect and Aspect are both variables I made up.
Aspect is the 'desired' aspect (85/230)
PicAspect is the picture's aspect (ht/width).