Solved VBA Load an Image based on Cell value

[XelaIrodavlas]

Hello,

Excel Amateur here. I have table with columns: UserName, {Miscellaneous Details}, and the Country they live in (a 3 letter code like USA, ITA, THA). I would like to programmatically add an image of the national flag for these countries in an adjacent column, can anybody help?

I've tried LeGoog, and to be honest it was flooded with copy-paste click-bait all suggesting an Index-Match within a named Range. While this technically works, it is really only practical for maybe one to ten records, as I would have to copy and amend the Index for every User. I need something up-scalable, for potentially hundreds of names.

Any ideas?

Many many thanks,
Alex

 

[June7]

Programmatically insert flag image from where? You have a folder with images? Images have names like USA.jpg?

Images are not actually in a column or cell, they sit on worksheet. If you just want to install a hyperlink into cell, that is different.

I am not sure what you mean by "have to copy and amend the Index for every User".

Why is {Miscellaneous Details} within braces?

 

[Isaac]

Hello,

Excel Amateur here. I have table with columns: UserName, {Miscellaneous Details}, and the Country they live in (a 3 letter code like USA, ITA, THA). I would like to programmatically add an image of the national flag for these countries in an adjacent column, can anybody help?

I've tried LeGoog, and to be honest it was flooded with copy-paste click-bait all suggesting an Index-Match within a named Range. While this technically works, it is really only practical for maybe one to ten records, as I would have to copy and amend the Index for every User. I need something up-scalable, for potentially hundreds of names.

Any ideas?

Many many thanks,
Alex

I would be curious of which recommendation you found that you felt would only work for a few users and that you would have to adjust the index. Can you post that code or formula? I am doubting the veracity of that conclusion that's my guess but I of course I don't know for sure it's just an intuition

 

[arnelgp]

use the technique in this youtube:

(1) Excel VLOOKUPs With Pictures! - YouTube

 

[strive4peace]

hi XelaIrodavlas

I found a site with images you can use -- https://flagpedia.net

the flag names are based on the 2-character country code though, so you have to either find another site or convert the 3-character codes to 2-character codes, or just use 2 characters in your data.

attached is a workbook with this code:

Sub callInsertFlags()
   Call InsertFlags( _
      ActiveWorkbook.Sheets("Data").Range("B2:B6") _
      ,-1)
End Sub

Sub InsertFlags(oRangePicture As Range _
   ,iColumnOffsetCtry As Integer)
'220517 strive4peace
'reference site: https://flagpedia.net/download/api
'some available sizes (there are more)
   '16x12
   '20x15
   '24x18
   '28x21
   '32x24 -- row height ~ 20 -- this is the one I used
   '36x27
   '40x30

   Dim oCell As Range
   Dim sCtry As String
   Dim sURL As String
   Dim oPicture As Picture

   For Each oCell In oRangePicture
      With oCell
         'get 2-character country code
         'same row, column offset
         sCtry = .Offset(0,iColumnOffsetCtry)
         'construct URL
         sURL =  "https://flagcdn.com/32x24/" & sCtry &  ".png"
         'insert picture on worksheet (parent of cell)
         Set oPicture = .Parent.Pictures.Insert(sURL)
         'position the picture
         oPicture.Top = Rows(.Row).Top
         oPicture.Left = Columns(.Column).Left
    
      End With  'oCell

   Next oCell

   MsgBox  "done"

End Sub

The first sheet is FlagsDone and shows what the code does.

To run the code, modify callInsertFlags for the range of data to put pictures in, and the column offset with the 2-character country code