Results 1 to 7 of 7

Thread: Display image based on a cell value from directory by vba

  1. #1
    Seeker MOUSA's Avatar
    Join Date
    Feb 2019
    Posts
    14
    Articles
    0
    Excel Version
    2007 or 2010

    Display image based on a cell value from directory by vba



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    I found the following video to be excellent.

    https://trumpexcel.com/picture-lookup/

  3. #3
    Seeker MOUSA's Avatar
    Join Date
    Feb 2019
    Posts
    14
    Articles
    0
    Excel Version
    2007 or 2010
    Quote Originally Posted by Heyjoe View Post
    I found the following video to be excellent.

    https://trumpexcel.com/picture-lookup/
    hi

    thanks for the reply
    but need to have macro for 100 line items to change once any cell value change
    thanks

  4. #4
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    135
    Articles
    0
    Excel Version
    365
    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?

  5. #5
    Seeker MOUSA's Avatar
    Join Date
    Feb 2019
    Posts
    14
    Articles
    0
    Excel Version
    2007 or 2010

    display image based on the cell value change

    Quote Originally Posted by Kenneth Hobson View Post
    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...hotogenic-cat/

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

    Thanks
    Last edited by MOUSA; 2019-03-25 at 06:56 AM. Reason: forget to add file type

  6. #6
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    135
    Articles
    0
    Excel Version
    365
    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 by Kenneth Hobson; 2019-03-25 at 04:14 PM.

  7. #7
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    135
    Articles
    0
    Excel Version
    365
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •