Insert picture based on cell reference

[Arnolf]

Hello,

I found the next link that is exactly what I am looking for, except that my photos are in a folder (+500 pictures).
So, instead to paste my pictures in The Worksheet, I'd rather to insert them directly from my location photo files that is D:\DataPic\

http://www.ozgrid.com/forum/showthread.php?t=35427

My range of the photo names listed goes from A2 to A502
Example:
A2: Apple
A3: Watermelon
A4: Lemon
.
.
A502: Strawberry

I need to show the photo in the range F3:G7 for the cell referenced in A2:A502 (the photo should be inserted at a fixed size, regardless of its original size).
Every time I'd select another name, the before picture selected (loaded) should be deleted.
Please, help me.
rgds,
Arnolf.

[iwrk4dedpr]

Arnolf,

Have you tried recording a macro? It would be a good place to start. With the recorded macro you would need to change just a couple of things.

1. You would need to determine how you want the macro to execute.
2. Maybe a loop to delete all picture shapes on the current page and then it would run the recorded part adding the new picture.

[Fin Fang Foom]

How about insert comment pictures ?


Place this code in cell B2

="C:\MyPicture\"&A2&".jpg"


And run this code:

Option Explicit 
Sub Add_Comments() 
     
    Dim myPict As Object 
    Dim curWks As Worksheet 
    Dim myRng As Range 
    Dim myCell As Range 
     
    Set curWks = Sheets(1) ' Change to suit
     
    With curWks 
        Set myRng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)) 
    End With 
     
    curWks.Columns("F").ClearComments 
     
    For Each myCell In myRng.Cells 
        If Trim(myCell.Value) = "" Then 
             'do nothing
        ElseIf Dir(CStr(myCell.Value)) = "" Then 
             'picture not there!
            MsgBox myCell.Value & " Doesn't exist!" 
        Else 
            With myCell.Offset(0, 0) '3 columns to the right of C (F)
                .AddComment("").Shape.Fill.UserPicture (myCell.Value) 
            End With 
        End If 
    Next myCell 
End Sub 

[Arnolf]

Thanks for your replies.
I enclose the file.
Instead to paste my pictures in the Worksheet, I'd rather to show them directly from my location photo files that is D:\DataPic\
rgds,
Arnolf

[Krishnakumar]

Hi,

Try this ShowPicD UDF by Damon.

HTH

[Fin Fang Foom]

I'm intrigued. I like your method by clicking the cells the picture appears, But instead of the picture appears in the cells how can it be modified to have the pictures appear in the image control box ?