Resizing Images Brought in Through VBA (Solved)

[DaveC3BI]

I am trying to set it up so that if a user checks a check box, it makes a sheet visible and inserts an image, scaled to fit within a certain area. It works just fine for any image that is landscape oriented where the height is 75% of the width (or less). But when I insert a portrait image I am having issues. At first it was making it too tall, so I adjusted the code to account for the height (and then center the image in the viewport), but now the portrait images lose their aspect ratio and look distorted. I have ShapeRange.LockAspectRatio = msoTrue, so it shouldn't be able to alter the aspect ratio, right?

Here is the code I have so far.

Please Login or Register  to view this content.

What am I missing here? Thanks!!

[DaveC3BI]

I just want to bump this, since it went unanswered yesterday and probably won't get noticed if I leave it on the 3rd page (and falling)...

[DaveC3BI]

Well I figured out my problem. I figured I should put the solution here in case anyone else ever has this issue and searches for it...

I had hidden all cells outside of the formatted area of the output worksheets. When you hide the cells, even if the lockaspectratio is set to True, an image height or width will be shrunk to fit the space. Once the aspect ratio is distorted, there didn't seem to be a way change it back... So I unhide the extra empty cells. I also simplified my code to look like this:

Please Login or Register  to view this content.

Basically, I insert the picture full size to ensure the aspect ratio holds true. Then I check to see if the width or the height will fill the required range (basically to check portrait vs landscape). If the width will fill the range, I change the width to the max (577.5), then center the image vertically. If instead the height will fill the range, then I set the height to the max (345) then center the image horizontally.

Hope this helps.