Image in Comment Based of Cell Value

[Karnage1210]

VB/Macro help! I am trying to create a way for a cell value which has a comment box in it as well to pull an image from a file destination based off the input in that cell. If someone types in a product ID into the cell the comment box will pull the image of the product on file.

So far I have been able to piece together something which allows me to run a macro for the specific active cell, set 1 specific image, but how do I enhance this to allow it to look for multiple images to drop into the comments based on the different cell values?

Sub Comments_Pic()
 Dim PicComments As Comment
 Dim LArea As Long
 Dim PicturePath As String
 Dim CellValue As Long
 
 CellValue = ActiveCell.Value
 
 'Explicitly Call Out The Image File Path
   PicturePath = "R:\DestinationFolder\" & CellValue & ".jpg"
 
 For Each PicComments In ActiveSheet.Comments
  
 With PicComments
    .Shape.Fill.UserPicture (PicturePath)
 End With
 Next
 End Sub

I'm too rusty with VB

 

[Momentman]

If i understand you correctly, then maybe you need a lookup table, a table with a Value in one column and a filepath in the other, except of course the filepath always incorporates the value of the cell or name of the object as it were

 

[Karnage1210]

The path to image should never change. It will always be a product number in the same folder followed by a .jpg so not sure if I need a lookup table. The code above sets all comment boxes to have the same image. I want each to be unique based on the cell where the comment is.

 

[Momentman]

The path to image should never change. It will always be a product number in the same folder followed by a .jpg so not sure if I need a lookup table. The code above sets all comment boxes to have the same image. I want each to be unique based on the cell where the comment is.

Your comment makes it a little clearer what i think you want to do

I have modified the code as below

Sub Comments_Pic()
    Dim PicComments As Comment
    Dim LArea As Long
    Dim PicturePath As String
    Dim CellValue As Long
    
    For Each PicComments In ActiveSheet.Comments
        CellValue = PicComments.Parent.Value
        PicturePath = "R:\DestinationFolder\" & CellValue & ".jpg"
        With PicComments
           .Shape.Fill.UserPicture (PicturePath)
        End With
    Next
End Sub

 

[Karnage1210]

That worked! But if I don't have a file for the image is there way to default to image I would like?

 

[Momentman]

That worked! But if I don't have a file for the image is there way to default to image I would like?

Maybe something like this

Sub Comments_Pic()
    Dim PicComments As Comment
    Dim LArea As Long
    Dim PicturePath As String
    Dim CellValue As Long
    
    For Each PicComments In ActiveSheet.Comments
        CellValue = PicComments.Parent.Value
        PicturePath = "R:\DestinationFolder\" & CellValue & ".jpg"
        If Dir(PicturePath, vbDirectory) = vbNullString Then
            PicturePath = "Whatever path you want in here"
        End If
        With PicComments
           .Shape.Fill.UserPicture (PicturePath)
        End With
    Next
End Sub

Essentially to check if the file exists using the DIR command and set it to whatever path you want

 

[Karnage1210]

Awesome! I think I can handle the rest with formatting the size of each comment box to be the same. Much appreciated!

 

[Karnage1210]

Curious though if I can do the same but with a website image. I just don't think I can put a web image into the comment box though. I have a function checking to see if the link exists on the web but I m getting a memory error when I try to run it.

If URLWorks(PicturePath) Then
            PicturePath = "[URL="http:///sku-img/"]http://[/URL]websiteaddress.com/images/" & CellValue & ".jpg"
            
        Else
            PicturePath = "R:\images\default_image.jpg"
        End If