Insert picture into cell, resize (keep aspect ratio) help

Hello all,

First time poster so please be gentle
I am trying to make an intelligent inspection report template which takes a lot of user error out of data entry. One of the features i am attempting is a photogrpahs page where the user can simply double click to add a photo into eight photo "frames" (merged cells).
Despite having zero experience with VBA i have attempted to piece-meal the code together myself from internet sources but it never comes out right!

Heres what i've got so far.
Im using this code in the photographs spreadsheet "view code" section to trigger the photo insert script:

Please Login or Register  to view this content.

Then i have piece-mealed the photo insertion code together below:

Please Login or Register  to view this content.

The problem is the photo insert code doesnt work as i want it.
I basically just need landscape and portrait photographs to be inserted into the merged cells of the first code but resize themselves to fit whilst maintaining aspect ratio

Can anyone help please?

See my screenshot above showing how the photo page of my report template looks and the outcome of the code

I don't like merged cells either which is why I made the width of every report page 34 columns of 1.78 - this was to avoid merged cells on the front page but I found that a single cell for a photo (i.e. merged) looked more pleasing than lots of little ones

I'd like for the user to be able to add any photo - portrait or landscape to the photo page and it fits them in suitably. This makes the photo page an easy one template rather than different templates for different orientated photos which could go on forever

Ok that seems to have improved it but its still not right I'm afraid
Now the first two photo boxes limit landscape photos on width but they overlap on height
All other photo boxes take regular landscape photos well except for wide angle shots - those which are wider than a normal landscape photo it does not preserve aspect ratio (see the photos at the bottom of my screenshot below, they are wide angle. the one on the left is imported with code and the one on the right is imported and resized manually)
As for portrait there is still a bug. In the first two boxes the photos seem to maintain aspect but are not resized to fit the frames
All other photo boxes portrait photos are resized but aspect ratio is lost... plus they are not stretched to the height limits of the cell

Really appreciate you helping me like this I'm sure you've got better things to do than help some Excel pleb

Broken Photo Code3.jpg

My apologies I didn't see your sample xls in your last post. Have tried again now on your sample xls inserting the new code in your last post
Landscape now appears perfect!!!
For some strange reason in my file the photo frames on photos 5, 6, 7 and 8 are doing something weird with the height of landscape photos - that's why when I manually imported a photo and put it next to it the code imported one was not correct. I was keeping ratio by the way. I've now imported your spreadsheet into mine and binned my original photos page.

As for portrait there's still an issue. Here's what it looks like using your sample and newest code:

Broken Photo Code4.jpg

Ok I added the replacement code starting at With...
Ran it and heres the debug it spits out:

Please Login or Register  to view this content.

Doesn't look a lot to me but I did follow your instructions correctly
EDIT
i ran it on two of those purple coloured portrait photos you've seen above

Ahhhh the last photo I attached above there was one of the oddball portrait images that doesn't fit correctly into the frame
It looks as though the pic was taken as a landscape (horizontally) but windows 10 is automatically rotating them

Do you think this is the case? I wonder how I can have my inspectors work around this when they use the report template

The picture inserts portrait after the code, it also inserts portrait when I manually insert the same photo so I guess however the photo appears in windows explorer is how excel takes

Any ideas on how to overcome this? Actually the PC's we are using in work run W7 which doesn't include the auto rotate "feature" but I hear my company will be upgrading to W10 so

Really appreciate all the help you've given me on this

Yes I see your point about knowing whether something has to be rotated or not. I don't think there is a solution really but the cameras we have onsite are brand new... they look like an iPhone and most likely have a gyroscope in them to know which way they are being orientated. I would hope when an inspector takes a portrait photo with these cameras windows will treat them as "official" portrait photos rather than a landscape photo that needs rotating (and vice versa with ordinary landscape photos). In other words tere might not be any of these oddball portrait photos coming up

I think i'll live with how it is now and do some trials with our digital cameras to see what sort of results come out in the report template
Fantastic help, hope this also helps anyone else who is trying to build the same function into one of their spreadsheets

All the best!

(FYI rotation for oddball portrait photos is set to 90deg when I import through the code or manually, so a rotation is picked up)

Just so you know I am seeing some files rotated at 270deg
Reading up its all about how windows and other programs interprets the EXIF data contained within photographs.
If we could somehow read whether the EXIF rotation data before we make our resizing that would be the winner

Just googling around these things pop up:
Someone who has written a load of VB code to read the EXIF data (looks crazy complicated): http://www.vb-helper.com/howto_net_r...ientation.html
A little program I could use to rotate all photographs before import somehow: https://www.slimjet.com/jpeg-rotate/...g-pictures.php

I logged onto my windows 7 PC and looked through all my photographs - there are a lot which are now auto rotated in W10 which obviously now ahs the capability to read EXIF and rotate direct in explorer
Unfortunately I suspect even our brand new digital cameras onsite will only embed EXIF data and wont pre-rotate photos
If this is the case I may have to resort to using manual means for importing photos into the reports

Ok thanks let me know what you hear

I just checked on my W7 machine... running the code or importing manually (insert-pictures) rotates photographs to portrait. In other words it doesn't matter whether I run the report on a W7 or W10 OS.... excel has the ability to read EXIF data, identify pictures that were taken portrait and rotate on import
We need code to take dimensions after the rotation and then do our resizing to them rather than the original in-rotated dimensions. The question is how...

if anyone has experience here please chime in

FYI I'm running excel 2013