Picture inserted in wrong position

[ashley12]

Hi everyone,

I've inserted pictures into my spreadsheet using the code below. I want to keep the aspect ratio, and have the top left of the picture starting from cell H20. It works fine with most. However a few pics instead of sitting nicely in that corner, they just float randomly (to the right and up from H20). And their top left corner doesn't fit into any cell, it's just in the middle. Do you know what could be the problem here? Thank you for your time

Sub Present_Pic()
    If PicPath = "" Then Exit Sub
    
    Dim Pic As Picture, ImageCell As Range

    Set ImageCell = Interface.Range("H20")

    Set Pic = Interface.Pictures.Insert(PicPath)
    With Pic
        .Left = ImageCell.Left
        .Top = ImageCell.Top
        .Width = 400

    End With
    
    Set Pic = Nothing

End Sub

 

[Anthony47]

Try with this different sequence:

    With Pic
        .Width = 400
        .Left = ImageCell.Left
        .Top = ImageCell.Top
    End With

Bye

 

[ashley12]

Thanks Anthony. The position slightly changed but still not where I want it to be ?

 

[Roncondor]

don't know if this will work , but try going to cell H20 application.goto reference and see if that works

FYI - always better using a range name rather than hardcoded cell of H20

If you move data, add rows or columns the picture could end up in the wrong place

 

[Anthony47]

Do you resize somewhere else the picture? Does this happen with any picture you insert there? Is Interface a reference to the current sheet? How has it been set?
Anyway, to collect debug information add these 3 "Debug.Print" lines

    With Pic
        .Width = 400
        .Left = ImageCell.Left
        .Top = ImageCell.Top
        Debug.Print Now, PicPath
        Debug.Print .Left, ImageCell.Left, ActiveSheet.Range("H20").Left
        Debug.Print .Top, ImageCell.Top, ActiveSheet.Range("H20").Top
    End With

Then when your picture is bad positioned open your vba and open the "Immediate Windows" (using Contr-g should be sufficient, or use Menu /View /Immediate window); then copy the last 3 lines (from a time stamp + picture path & name); double check (using the time stamp and the picture path & name) it is the failing picture; include these lines in your next message.

Also, before going on, do you know that .Pictures.Insert will insert in your worksheet a link to the image (don't embed it with the sheet), so if you open the workbook on another pc very likely images will not be shown? The alternate method for embedding is .AddPicture

Bye