Excel VBA Picture insert but can't be shared with another computer

[Logihk]

Hi experts!

I have an Excel VBA that will look up a cell value and insert the picture in a designated cell in return. I have already included the LinkToFile:=msoFalse and SaveWithDocument:=msoTrue. However, I still can't see the photos when I open the file in another computer.

Can you all help me take a look at my VBA codes and see where did I do wrong? Does it also matter which Excel format I save the file in?

Thanks a lot in advance!!!!

Below is the VBA Code:



Private Sub Worksheet_change()

Dim myPict As Picture
Dim PictureLoc As String

If Target.Address = Range("B2").Address Then

PictureLoc = "C:\Users\Paul.Man\Pictures\Product pics\" & Range("B2").Value & ".png"

With Range("A2")
Set myPict = ActiveSheet.Shapes.AddPicture( _
PictureLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Top:=imgTop, _
Left:=imgLeft)
End With

End If

End Sub

 

[Micron]

AddPicture documentation states that width and height are required parameters, along with top and left - that's all I can think of. Perhaps it's linking because of the missing parameter and you don't know it. You've also reversed left and top - don't know if it matters with that style of code vs
AddPicture (FileName, LinkToFile, SaveWithDocument, Left, Top, Width, Height)

Please enclose more than a few lines of code within code tags (vba button on posting toolbar). Makes it easier to read and retains your indentation.

 

[Domenic]

It's interesting, though. Without the width and height, as mentioned by @Micron , you should have received an error regarding the wrong number of arguments.

By the way, if you want to set the width and height to their defaults, set both arguments to -1.

Also, I noticed that you have not defined the variables imgTop and imgLeft. Although, it looks like you probably want your image aligned with cell A2.

Maybe this is what you want?

Dim myPict As Shape

With Range("A2")
    Set myPict = ActiveSheet.Shapes.AddPicture( _
        Filename:=PictureLoc, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=.Left, _
        Top:=.Top, _
        Width:=-1, _
        Height:=-1)
End With

Notice that myPict is declared as a Shape, not Picture. Otherwise you'll get a type mismatch error.

 

[Logihk]

Hi Micron & Domenic, Thanks much for your reply. This is my first time posting on this forum and wasn't aware about the VBA button.

I have tried revising the code with both of your suggestion but it's still only saving as a link. Please kindly see the code I have amended as well as the screen capture of the file when I open with another computer.

I truly don't understand which step did I do wrong. Thanks much!

Private Sub Worksheet_change()

Dim myPict As Shape
Dim PictureLoc As String

If Target.Address = Range("B2").Address Then

PictureLoc = "C:\Users\Paul.Man\Pictures\Product pics\" & Range("B2").Value & ".png"

With Range("A2")
Set myPict = ActiveSheet.Shapes.AddPicture( _
Filename:=PictureLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left, _
Top:=.Top, _
Width:=-1, _
Height:=-1)
End With

End If

End Sub

 

[Domenic]

Just to be clear, are you saying that the pictures are displayed when you open the workbook on your computer, but they are not displayed when you open it on another computer?

 

[Logihk]

Just to be clear, are you saying that the pictures are displayed when you open the workbook on your computer, but they are not displayed when you open it on another computer?

Yes. After I performed the Macro, the pictures appear nicely in the cells of Column A. But when I saved the file and opened it with another computer, I can't see the pictures anymore. The last screen capture was the saved file being opened on another computer.

Thanks much for your help!

 

[Domenic]

Try opening a completely new workbook, and run the following line, replacing the path and filename accordingly. Does the image display on your other computer?

ActiveSheet.Shapes.AddPicture Filename:="c:\path\filename.jpg", LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=20, Top:=20, Width:=-1, Height:=-1

I'm logging off shortly, so I may not be able to look at this further until sometime tomorrow morning.

 

[Logihk]

Try opening a completely new workbook, and run the following line, replacing the path and filename accordingly. Does the image display on your other computer?

ActiveSheet.Shapes.AddPicture Filename:="c:\path\filename.jpg", LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=20, Top:=20, Width:=-1, Height:=-1

I'm logging off shortly, so I may not be able to look at this further until sometime tomorrow morning.

Thanks Domenic. I will give a try. Have a nice evening!

 

[Domenic]

You too, have a nice evening.

Cheers!

 

[Logihk]

Hi Domenic, it's working and I can see the 1 photo on the other computer. Below is the VBA Code used:

Sub Picture_insert()

Dim myPict As Shape
Dim PictureLoc As String

PictureLoc = "C:\Users\Paul.Man\Pictures\Product pics\4061504002057.png"

Set myPict = ActiveSheet.Shapes.AddPicture( _
Filename:=PictureLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=1, _
Top:=1, _
Width:=40, _
Height:=40)

End Sub

However, once I start putting the IF... THEN.. together with RANGE, the picture goes back to being a link and not showing on the other computer again. The "Run-Time error '424': Object required" showed up.

Sub Picture_insert()

Dim myPict As Shape
Dim PictureLoc As String

If Target.Address = Range("B2").Address Then

PictureLoc = "C:\Users\Paul.Man\Pictures\Product pics\" & Range("B2").Value & ".png"

With Range("A2")
Set myPict = ActiveSheet.Shapes.AddPicture( _
Filename:=PictureLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=1, _
Top:=1, _
Width:=40, _
Height:=40)

End With

End If

End Sub

Can you help me take a look if there is something wrong I have input for the additional code?

Thanks!!