-1

I'm new to VBA. I have an Excel file where in column A there is product number (ie 12345, 12346 etc); I want the attached picture from the web as the comment in the specific cell with item number.

Picture on the web is on https://www.website.com/picture/12345.jpg.

I have multiple product numbers in column A.

I did come up with something like that but this is not working.

Sub InsertMultipleCommentWithPicture()
  Dim i As Long
  Dim LastRow As Long
  Dim CommentRange As Range
  Dim Pic As Object
  Dim PicURL As String
  
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
  For i = 1 To LastRow
    Set CommentRange = Cells(i, 1)
    PicURL = "https://www.website.com/picture" & Cells(i, "A").Value & ".jpg"
    Set Pic = CreateObject("Scripting.FileSystemObject").GetFile(PicURL)
    CommentRange.AddComment
    With CommentRange.Comment
      .Shape.Fill.UserPicture Pic.Path
      .Shape.Width = Pic.Size * 0.01
      .Shape.Height = Pic.Size * 0.01
    End With
  Next i
End Sub
type here

Does someone already have something similar which can do this as I'm lost.

I did tried above code and some codes from internet however none of them working.

I need have the picture from website attached as the comment not inserted in the cell.

It will be good if there will be option to size up the comment box to specific size.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pawel
  • 1
  • 1
    What does "not working" mean? Are you getting an error? When you put a breakpoint on the start of the loop and check your variables, are they what you expect them to be? If so, then step (F8) it to the next line... check them again... and so on.... – braX Feb 02 '23 at 23:59
  • yes it's not working and i can't find a solution l i did try trouble shot as well line by line – Pawel Feb 03 '23 at 00:38
  • You first need to identify the problem by stepping through it. – braX Feb 03 '23 at 00:41

1 Answers1

0

UPDATE I missed that you were originally trying to insert the image in the cell's comment. Here is the updated code to do that.

Sub InsertMultipleCommentWithPicture()
  Dim i As Long
  Dim LastRow As Long
  Dim CommentRange As Range
  Dim Pic As Object
  Dim PicURL As String
  Dim cRange As Range
  Dim commentbox As Comment
  
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
  For i = 1 To LastRow
    Set cRange = Cells(i, "A")
    PicURL = "https://www.website.com/picture/" & cRange.value & ".jpg"
    'Remove Any Comments within cell
    cRange.ClearComments
    Set commentbox = cRange.AddComment
    'Remove Any Default Comment Text
    commentbox.Text Text:=""
    commentbox.Shape.Fill.UserPicture (PicURL)
    commentbox.Shape.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
    commentbox.Shape.ScaleWidth 0.5, msoFalse, msoScaleFromTopLef
  Next i
End Sub
Josh Bullough
  • 1
  • 1
  • Thanks for the comment , yes i'm fully ware about image() function but this is only inserting picture in the cell where i need get this picture insterted within comment box not cell. – Pawel Feb 03 '23 at 10:02
  • Sorry I missed that you were trying to get those images within a comment box. I have updated the code above to do that. – Josh Bullough Feb 04 '23 at 02:05