Insert JPG into cell based on cell value

[WayneTN]

Hello. I'm very new with coding and couldn't find what I need after multiple searches. I think the solution will be simple for someone who knows what they are doing.

I'm working on a spreadsheet to track the team scoring during the Ryder Cup golf matches.
I have 3 pictures of 3 different flags (USA, Europe, Halved) in a folder called C:\MyFiles\Flags\
The flags' files are named USA.jpg, Europe.jpg, Halved.jpg

During the golf matches, various cells on my spreadsheet's Scoreboard worksheet (within the range of C9:M17) will be populated from other worksheets with words saying either "USA", "EUROPE", or "HALVED" (whoever wins that match).

What I want to do is this. When a cell on my Scoreboard worksheet says "USA", I want my spreadsheet to pop into that cell the picture of the USA flag, and so on. The pictures are already sized for each cell, so there should be no problems there.

Any help is greatly appreciated.
Thanks in advance,
WayneTN

 

[RasGhul]

Hi Wayne,

Both Excelisfun and Leila G have done videos on this solution

Excel Picture Lookup: 5 easy steps for dynamic images

 

[jolivanes]

Or

Check and change references where required.
In the "Scoreboard" sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C9:M17")) Is Nothing Then
    Call Get_Flags
End If
End Sub

In a regular module.

Sub Get_Flags()
Dim shp As Shape, rng As Range, c As Range, flagArr, i As Long
Set rng = Sheets("Scoreboard").Range("C9:M17")
flagArr = Array("USA", "Europe", "Halved")

    For Each shp In Sheets("Scoreboard").Shapes
        If Not Intersect(shp.TopLeftCell, rng) Is Nothing Then shp.Delete
    Next shp
    
    For Each c In rng
        For i = LBound(flagArr) To UBound(flagArr)
            If LCase(c.Value) = LCase(flagArr(i)) Then
                ActiveSheet.Pictures.Insert("C:\MyFiles\Flags\" & flagArr(i) & ".jpg").Select

                    With Selection.ShapeRange
                        .LockAspectRatio = msoFalse
                        .Left = c.Left
                        .Top = c.Top
                        .Width = c.Width
                        .Height = c.Height
                    End With
            End If
        Next i
    Next c

End Sub

 

[jolivanes]

Change these 8 lines

ActiveSheet.Pictures.Insert("C:\MyFiles\Flags\" & flagArr(i) & ".jpg").Select
With Selection.ShapeRange
.LockAspectRatio = msoFalse
.Left = c.Left
.Top = c.Top
.Width = c.Width
.Height = c.Height
End With

to this single line

ActiveSheet.Shapes.AddPicture "C:\MyFiles\Flags\" & flagArr(i) & ".jpg", msoFalse, msoTrue, c.Left, c.Top, c.Width, c.Height

 

[jolivanes]

Can I delete the temp workbook created to test the code supplied to you?

 

[WayneTN]

Can I delete the temp workbook created to test the code supplied to you?

Yes, the temp workbook may be deleted. Thanks for your input.

 

[WayneTN]

Hi Wayne,

Both Excelisfun and Leila G have done videos on this solution

Excel Picture Lookup: 5 easy steps for dynamic images

Thanks for your valuable input. I watched the Leila G video where she explains how to insert a JPG into a cell based on programming (dynamic images), and I adapted her work to accomplish what I needed. She chose not to use VBA code but to only use Excel programming (indirect function, name management) to cause national flag pics to pop into cells. It was a little tricky making everything look neat and involved cropping and sizing the flags and cells as Leila explains in the video, but I made everything work. Now I have a Ryder Cup golf template with 28 cells ready to display the flag of the country that wins each of the 28 matches. Thanks everyone who supported my efforts!