Inserting an image

[killar65]

Good morning everyone...hopes this finds all well...

Prob a very easy one to solve...I've checked out Dr. Google but haven't quiet found what i'm looking for...

I have a worksheet called Rocks.
I have a folder on C:\ called rock pics

I would like an image to automatically load into cell B2, based on the contents in cell A2...ie...If the word granite is in cell A2, then I would like the granite image (in C:\rock pics) to automatically display in cell B2.

The picture image needs to be 100 x 100.

Any help would be greatly appreciatted.

Cheers,
James

 

[jolivanes]

Is the granite picture named "Granite.jpg"?

Re: The picture image needs to be 100 x 100. Meaning?

 

[killar65]

Is the granite picture named "Granite.jpg"?

Re: The picture image needs to be 100 x 100. Meaning?

Hi Jolivanes,

Yes, the picture is named granite.jpg

In regards to the size, the images contained in the folder are of different sizes, so I was looking to insert them in a more uniform manner if that makes sense.

 

[jolivanes]

Sub Like_So()
    With Sheets("Rocks")
        .Shapes.AddPicture "C:\Rock Picks\" & .Range("A2").Value, False, True, .Range("B2").Left, .Range("B2").Top, 100, 100
    End With
End Sub

Re: "more uniform manner" means size I assume.

 

[jolivanes]

Maybe the next question is how to do it for multiple pictures.

Sub Like_So_Multiple_Pics()
    Dim c As Range, fldr As String
    fldr = "C:\Rock Picks\"
    For Each c In ActiveSheet.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        ActiveSheet.Shapes.AddPicture fldr & c.Value, False, _
            True, c.Offset(, 1).Left, c.Offset(, 1).Top, 100, 100
    Next c
End Sub

 

[killar65]

Sub Like_So()
With Sheets("Rocks")
.Shapes.AddPicture "C:\Rock Picks\" & .Range("A2").Value, False, True, .Range("B2").Left, .Range("B2").Top, 100, 100
End With
End Sub

Re: "more uniform manner" means size I assume.

Hi Jolivanes,

Many thanks for that...I've tried running the first lot of code with no success...nothing loading.

 

[jolivanes]

Have you checked and confirmed proper spelling of all references? No accidental leading or trailing spaces in names?
It works like a charm on a test workbook here.

 

[killar65]

Have you checked and confirmed proper spelling of all references? No accidental leading or trailing spaces in names?
It works like a charm on a test workbook here

Certainly have checked...folder and sheet names ok...I put the name of the picture in A2, less the jpg file ext, and nothing in B2

 

[jolivanes]

In Post #6, did you notice the spelling of your folder name? (fldr = "C:\Rock Picks\")
I assume that there is an extra letter (k) in "Picks".

As for inserting pictures where the name in the cell does not have an extension, try following.
Check and change reference names if required.

Sub Without_Extension()
Dim fn As String, filename
        fn = Dir("C:\Rock Pics\" & Sheets("Rocks").Cells(2, 1).Value & "*")
            ActiveSheet.Shapes.AddPicture _
                filename:="C:\Rock Pics\" & fn, _
                linktofile:=msoFalse, savewithdocument:=msoTrue, _
            Left:=Cells(2, 2).Left, Top:=Cells(2, 2).Top, Width:=100, Height:=100
        filename = Dir
End Sub

 

[killar65]

In Post #6, did you notice the spelling of your folder name? (fldr = "C:\Rock Picks\")
I assume that there is an extra letter (k) in "Picks".

As for inserting pictures where the name in the cell does not have an extension, try following.
Check and change reference names if required.

Sub Without_Extension()
Dim fn As String, filename
        fn = Dir("C:\Rock Pics\" & Sheets("Rocks").Cells(2, 1).Value & "*")
            ActiveSheet.Shapes.AddPicture _
                filename:="C:\Rock Pics\" & fn, _
                linktofile:=msoFalse, savewithdocument:=msoTrue, _
            Left:=Cells(2, 2).Left, Top:=Cells(2, 2).Top, Width:=100, Height:=100
        filename = Dir
End Sub

Morning jolivanes...

Once again, thanks for your time and patience...

I've tried all the code variants you have provided with no luck on any of them...

I've put the codes 'behind' the sheet and within a module (a googled fix for VBA not working)...not sure if there is a setting on this machine that is stopping the code from working correctly, as you have bench tested prior to posting. Unfortunatley, I don't have another machine to test to see if it works on that.

I've modified folder names to suit and am happy that that is all fine...got me stumped...