• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Resize an image to fit cell dimensions

Roxhan

New Member
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...
Code:
    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

Well-Known Member
try something along the lines of:
Code:
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

New Member
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

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