Inserting Photos Via Macro

[overKBV]

I have the code below. It works fine with photos taken in landscape but not with photos in portrait. How can I adjust so it just takes whatever photo? It doesn't really matter what the photo looks like in the spreadsheet just so you can see it, click on it, and open the original.

Sub AddPictureNotInsert()
    Dim strFileName As String
    Dim objPic As Shape
    Dim rngDest As Range
    strFileName = Application.GetOpenFilename( _
        FileFilter:="Images (*.jpg;*.gif;*.png),*.jpg;*.gif;*.png", _
        Title:="Please select an image...")
    If strFileName = "False" Then Exit Sub
    Set rngDest = ActiveCell
    Set objPic = ActiveSheet.Shapes.AddPicture(strFileName, False, True, 10, 10, -1, -1)
    With objPic
        .LockAspectRatio = msoFalse
        .Left = rngDest.Left + 1
        .Top = rngDest.Top + 1
        .Width = rngDest.Width - 2
        .Height = rngDest.Height - 2
    End With
    
    ActiveSheet.Hyperlinks.Add Anchor:=objPic, Address:=strFileName
    
End Sub

 

[rlv01]

Your code sizes the photo to the destination cell. Small cell = small photo.

Start by changing the LockAspectRatio property to true.

.LockAspectRatio = msoTrue

 

[overKBV]

Okay, I tried that it did not make a difference. I also tried making the cell larger and that did not work. Is there a way to force the photo to be the same size without cropping it?

 

[rlv01]

Okay, I tried that it did not make a difference. I also tried making the cell larger and that did not work. Is there a way to force the photo to be the same size without cropping it?

That is odd, because it It makes a big difference (an enormous difference, in fact) when I run the exact code you posted. Perhaps you can describe the issue in more detailed terms. What happens when you use the F8 key in the VBE debugger to single step though your code? At which step does the image change to something you do not like?

 

[overKBV]

I did not describe the problem completely. Landscape photos are added to the cell fine, portrait photos do not show up at all. Like the cell remains empty. It's as if the photo is too large and just is not added. The code seemingly works fine for the landscape photos. Just not at all for the portrait photos.

 

[rlv01]

I tested your code on both landscape and portrait. No issues on either except the image was smashed into the size & shape of the activecell - but that is how you coded it. I still think you need to do the F8 single step test on both a portrait and landscape example.

 

[overKBV]

I tested your code on both landscape and portrait. No issues on either except the image was smashed into the size & shape of the activecell - but that is how you coded it. I still think you need to do the F8 single step test on both a portrait and landscape example.

So, I have deduced that it is not a portrait versus landscape problem. It must be a source problem that will take a little more investigation. Thanks for your time, I will get back to you if I figure out the problem

 

[rlv01]

It must be a source problem that will take a little more investigation.

That's what I would suspect. Good luck.