+ Reply to Thread
Results 1 to 6 of 6

VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empty

  1. #1
    Registered User
    Join Date
    01-24-2017
    Location
    Zagreb
    MS-Off Ver
    2013
    Posts
    9

    VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empty

    I have a statement with Image Dynamically In Cell (fom I11 to I19) I usually only use 2 or 3 lines, so the other 7-8 are empty. My macro command works fine in cells that are filled, but for empty cells it also inserts an image that says "The linked image cannot be displayed". I would like VBA not to insert an image if cell D11 to D19 is empty. Also, to run the command I have to click on the developer tab and macros, can this be automated (column D has data validation)?. Thank you

    CODE:

    Sub Insert_Multiple_Images()

    Set Image_Names = Range("D11:D19")
    Image_Location = "C:\Image"
    Image_Format = ".png"

    Set Cell_Reference = Range("I11:I19")

    For i = 1 To Image_Names.Rows.Count
    For j = 1 To Image_Names.Columns.Count
    Set Image = ActiveSheet.Pictures.Insert(Image_Location + "" + Image_Names.Cells(i, j) + Image_Format)
    Image.Top = Cell_Reference.Cells(i, j).Top
    Image.Left = Cell_Reference.Cells(i, j).Left
    Image.ShapeRange.Height = 45
    Image.ShapeRange.Width = 75
    Next j
    Next i

    End Sub

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empt

    Jus add one IF statement:
    PHP Code: 
                If .Value <> "" Then 
    final code should be:
    PHP Code: 
    Sub Insert_Multiple_Images()
    Set Image_Names Range("D11:D19")
    Image_Location "C:\Image"
    Image_Format ".png"
    Set Cell_Reference Range("I11:I19")
    For 
    1 To Image_Names.Rows.Count
        
    For 1 To Image_Names.Columns.Count
            Set Image 
    ActiveSheet.Pictures.Insert(Image_Location "" Image_Names.Cells(ij) + Image_Format)
            
    With Cell_Reference.Cells(ij)
                If .
    Value <> "" Then
                    Image
    .Top = .Top
                    Image
    .Left = .Left
                    Image
    .ShapeRange.Height 45
                    Image
    .ShapeRange.Width 75
                End 
    If
            
    End With
        Next j
    Next i
    End Sub 
    Quang PT

  3. #3
    Registered User
    Join Date
    01-24-2017
    Location
    Zagreb
    MS-Off Ver
    2013
    Posts
    9

    Re: VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empt

    Unfortunately it doesn't work
    Paste all images in one field (and in the one where it is positioned)
    Attached Images Attached Images

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empt

    error posting! pls ignore.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empt

    Sub Insert_Multiple_Images()
    Set Image_Names = Range("D11:D19")
    Image_Location = "C:\Image"
    Image_Format = ".png"
    Set Cell_Reference = Range("I11:I19")
    For i = 1 To Image_Names.Rows.Count
    For j = 1 To Image_Names.Columns.Count
    ' Set Image = ActiveSheet.Pictures.Insert(Image_Location + "" + Image_Names.Cells(i, j) + Image_Format)
    With Cell_Reference.Cells(i, j)
    If .Value <> "" Then
    Set Image = ActiveSheet.Pictures.Insert(Image_Location + "" + Image_Names.Cells(i, j) + Image_Format)
    Image.Top = .Top
    Image.Left = .Left
    Image.ShapeRange.Height = 45
    Image.ShapeRange.Width = 75
    End If
    End With
    Next j
    Next i
    End Sub

  6. #6
    Registered User
    Join Date
    01-24-2017
    Location
    Zagreb
    MS-Off Ver
    2013
    Posts
    9

    Re: VBA Insert Picture Dynamically In Cell Based On Cell Value and do nothing if cell empt

    I got the same result

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-08-2021, 10:27 PM
  2. [SOLVED] Insert picture based on cell value
    By abdumon7 in forum Excel General
    Replies: 2
    Last Post: 11-15-2017, 08:38 AM
  3. [SOLVED] Insert picture based on cell value
    By johandenver in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-12-2017, 11:10 AM
  4. [SOLVED] Insert picture based on cell value
    By magman1984 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-19-2015, 10:05 AM
  5. [SOLVED] Insert Picture in cell, click commanbutton to rename picture, lock cell and remove button
    By thecdnmole in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2014, 10:05 PM
  6. Macro to insert picture in cell based off a value in same cell
    By Greybeard84 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2014, 06:36 AM
  7. Insert Picture inro Excel and dynamically adjusting the cell size
    By adelkam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-03-2009, 02:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1