Display image based on a cell value from directory by vba

MOUSA

New member
Joined
Feb 21, 2019
Messages
14
Reaction score
0
Points
0
Excel Version(s)
2007 or 2010
HI,

PLS. assist in getting VBA code for the below task.

i have 200 line items code according in H COLUMN AND i want to display images in J column according to the cell value from my image folder from DESKTOP.


thanks.
 
What is the file type, JPG, GIF, BMP, PNG, etc.?

Would column H values have the filename like Apple or Apple.jpg or?

Would the image be resized to the cell's height and width or just to a set height or width and then resize other dimensions to maintain aspectratio?

Would the inserted image be a link to the image file or embedded into Excel independent of the file?
 
display image based on the cell value change

What is the file type, JPG, GIF, BMP, PNG, etc.?

Would column H values have the filename like Apple or Apple.jpg or?

Would the image be resized to the cell's height and width or just to a set height or width and then resize other dimensions to maintain aspectratio?

Would the inserted image be a link to the image file or embedded into Excel independent of the file?



Hi
Many thanks for your reply

Example like [ A Column will have the item code A-APPLE , B-BANANA , C-CAT ETC. ]

Once the code in A column the corresponding image / picture from PIC-IMAGE FOLDER from desktop should bring to column B

NORMAL OR SMALL IMAGE SIZE AND DIMENTSIONS .

IMAGE HEIGHT = 80
WIDTH = 70

BASED ON THE CELL VALUE CHANGE IT WILL DISPLAY THE IMAGE FORM THE FOLDER.

OR

BASED ON THE CELL VALUE CHANGE PICTURE FROM THE IMAGE PATH FROM GOOGLE / INTERNET Directly.

EXAMPLE IMAGE PATH : path will be in column C.
https://www.usmagazine.com/celebrity-news/news/meet-smoothie-worlds-most-photogenic-cat/

file type, JPG, GIF, BMP, PNG, etc.? ALL Types of form

Thanks
 
Last edited:
The file type must be known or one can guess and check for the first one that "might" exist. Of course Apple.jpg may not be the same image as Apple.png.

If would help if you attached a short example file with one of each type: desktop\image folder file and url. Manually paste and resize as needed.

If you are going to force both width and height, if the original file does not have the same ratio w/h = 70/80, distortion will occur.
 
Last edited:
This is for the Desktop's subfolder Image file.

Right click the sheet's tab, View Code, and paste:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)  
  Dim p$, fn$, s As Shape, r As Range, c As Range, j As Range
  Dim a, e
  
  Set r = Intersect(Columns("H:H"), Target)
  If r Is Nothing Then Exit Sub
  If r.Column <> 8 Then Exit Sub 'Column H=8
  
  p = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Image\"
  'https://support.office.com/en-us/article/graphic-file-types-you-can-insert-and-save-dad53574-3384-4ced-b472-348d37c326a7
  'https://en.wikipedia.org/wiki/Image_file_formats
  a = Split("jpg,gif,bmp,png,tif,tiff", ",")
  
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  For Each c In r
    For Each e In a
      fn = p & c & "." & e
      If Dir(fn) <> "" Then
        Exit For
        Else
        fn = ""
      End If
    Next e
    
    On Error Resume Next
    ActiveSheet.Shapes("Pic" & c.Address(False, False)).Delete
    On Error GoTo 0
    
    If fn <> "" Then
      Set j = Cells(c.Row, "J")
      j.RowHeight = 80
      'Set s = ActiveSheet.Shapes.AddPicture( _
        fn, msoFalse, msoCTrue, j.Left, j.Top, j.Width, j.Height)
      Set s = ActiveSheet.Shapes.AddPicture( _
        fn, msoFalse, msoCTrue, j.Left, j.Top, 70, 80)
      s.Name = "Pic" & c.Address(False, False)
    End If
  Next c
  
  Application.DisplayAlerts = True
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub
 
Back
Top