How to insert jpg images into spreadsheet using VBA

[EMASOFT]

thank you for your prevoius codes
i wanted a vba code that picks a photo from folder basing on a drop down list at ref- U13 and display it at ref P2 and if the drop option has no image in the folder, a blank image be displayed for that option without returning any error message
the code i am having is

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Dim myPict As Picture
Dim PictureLoc As String

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

ActiveSheet.Pictures.Delete

PictureLoc = "C:\PHOTO\" & Range("U13").Text & ".jpg"

With Range("P2")
On Error GoTo errormessage:
Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)

myPict.Height = 300
myPict.Width = 200
myPict.Top = .Top
myPict.Left = .Left
myPict.Placement = xlMoveAndSize
myPict.ShapeRange.LockAspectRatio = msoTrue

errormessage:
If Err.Number = 1004 Then

MsgBox "File does not Exist, Please first update photo with .jpg File"

End If
End With
End If
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

Re: "folder basing on a drop down list at ref- U13"
What does the drop down list show? Full path and picture name with extension?

 

[EMASOFT]

Re: "folder basing on a drop down list at ref- U13"
What does the drop down list show? Full path and picture name with extension?

the picture are in the folder on desktop named PHOTO containing photos of students named according to their registration numbers
on the spreadsheet, students reg number is at U13 and the images is to be displayed at ref-P2

 

[jolivanes]

Yes, I just noticed.
Normally I don't bother with code that does not work according to the OP.
Everybody has their own way of doing (solving) things.

 

[jolivanes]

Check and change references if required.
Save attached picture into the PHOTO folder and name it NoPic.jpg

Sub Add_Pic()
If Dir("C:\PHOTO\" & Cells(13, 21).Value & ".jpg") <> "" Then
    ActiveSheet.Shapes.AddPicture("C:\PHOTO\" & Cells(13, 21).Value & ".jpg", False, True, Cells(2, 16).Left, Cells(2, 16).Top, 200, 300).Name = "Pic " & Cells(13, 21).Value
        Else
    ActiveSheet.Shapes.AddPicture("C:\PHOTO\NoPic.jpg", False, True, Cells(2, 16).Left, Cells(2, 16).Top, 200, 300).Name = "NoPic"
End If
End Sub

 

[EMASOFT]

Check and change references if required.
Save attached picture into the PHOTO folder and name it NoPic.jpg

Sub Add_Pic()
If Dir("C:\PHOTO\" & Cells(13, 21).Value & ".jpg") <> "" Then
    ActiveSheet.Shapes.AddPicture("C:\PHOTO\" & Cells(13, 21).Value & ".jpg", False, True, Cells(2, 16).Left, Cells(2, 16).Top, 200, 300).Name = "Pic " & Cells(13, 21).Value
        Else
    ActiveSheet.Shapes.AddPicture("C:\PHOTO\NoPic.jpg", False, True, Cells(2, 16).Left, Cells(2, 16).Top, 200, 300).Name = "NoPic"
End If
End Sub

Thank you so much.
how do i use this code?
because it not executing the required output
do i integrate it in my former code?
how do i interprete Cells(13,21)?
thank you

 

[jolivanes]

Do you want it to work when selecting from the drop down?
It has nothing to do with your code. It replaces the code you showed in the first post.
Cells(13, 21) is the same as Range("U13"). The 13 is the Row number and the 21 is the column number.

Copy this into the module for the sheet where the drop down is. Change the "Sheet1" to your actual sheet name.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Cells(13, 21) Then Sheets("Sheet1").Pictures.Delete: Add_Pic
End Sub

In a regular module, paste the following code. Again, check all references and change as required.

Sub Add_Pic()
If Dir("C:\PHOTO\" & Cells(13, 21).Value & ".jpg") <> "" Then
ActiveSheet.Shapes.AddPicture("C:\PHOTO\" & Cells(13, 21).Value & ".jpg", False, True, Cells(2, 16).Left, Cells(2, 16).Top, 200, 300).Name = "Pic " & Cells(13, 21).Value
Else
ActiveSheet.Shapes.AddPicture("C:\PHOTO\NoPic.jpg", False, True, Cells(2, 16).Left, Cells(2, 16).Top, 200, 300).Name = "Pic " & "NoPic"
End If
End Sub

To paste code into a worksheet module:
Right mouse click on the sheet where the macro needs to work on. In your case, the sheet that has the drop down.
Select "View Code"
Delete the existing code or comment it out by putting an apostrophe in front of every line.
Paste the top code (the 3 lines macro) in the large white area on the right. On the left side your sheet in question will be highlighted.
At the top, click on "Insert" and "Module"
In the large white area paste the other (Add_Pic) macro.
Go back to your sheet with the drop down, safe your workbook and select from your drop down.
I assume you copied the "NoPic" picture into the PHOTO folder as mentioned previously.

How to Add Macro Code to Excel Workbook

How to copy Excel macro VBA code to your workbook, from website or sample file. Different types of code, where to paste it. Step-by-step videos, written steps

www.contextures.com

 

[jolivanes]

Change the worksheet_change macro to this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "U13" Then Sheets("Sheet1").Pictures.Delete: Add_Pic    '<---- Change sheet name as required and check cell reference
End Sub

 

[jolivanes]

Better yet. Replace the code from Post #8 with this.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("U13")) Is Nothing Then Sheets("Sheet1").Pictures.Delete: Add_Pic    '<---- Change sheet name to actual
End Sub

 

[EMASOFT]

thank you so much it has worked perfectly

however i did not get the option to define image dimensions