Inserting Pictures into Comment Box with VBA

[lp3eb]

Hi everyone,

So here is what I would like to do:

1 - Highlight certain cells in a worksheet. Typically a sequence of cells in a column -- top to bottom
2 - Run my macro at which time the dialog file picker pops up and I select a series of images from a folder on my hard drive. The number of images is equal to the number of cells highlighted
3 - Macro inserts comments in each cell
4 - Macro formats (sizes) each comment accordingly
5 - Macro inserts picture into each comment in sequence (eg first picture goes into first cell's comment, second picture goes into second cell's comment, etc)

So far I've gotten through item #4 without a problem. The tricky part for me is how to fill each comment with a corresponding picture.
Any ideas??

Here is my code so far:

Sub PictureInComments()
    Dim n As Integer
    Dim i As Integer
    Dim cmt As Comment
    Dim c As Range
    Dim rng As Range
    Dim item
    
    On Error Resume Next
    
    i = 1
    
    Set rng = Application.Selection
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Title = "Select Images"
        .ButtonName = "Select"
        .Show
        For Each c In rng
            n = i
            c.AddComment
            Set cmt = c.Comment
            If Not cmt Is Nothing Then
                With cmt.Shape
                    .Height = 162
                    .Width = 296
                    For Each item In .SelectedItems
                        .Fill '***HERE I WANT TO PLACE THE FIRST PICTURE IN THE FIRST CELL, THEN SECOND PICTURE IN THE SECOND CELL, ETC BUT HOW DO I DO THAT?***
                    Next item
                End With
            End If
        Next c
        i = i + 1
    End With


End Sub

 

[lp3eb]

Well I suppose I was able to work it out on my own. It works great. Here is my final code:

Sub PicsInComments()
    Dim n As Integer
    Dim i As Integer
    Dim cmt As Comment
    Dim c As Range
    Dim rng As Range
    Dim strPic As String
    
    On Error Resume Next
    Set rng = Application.Selection
    i = 1
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Title = "Select Images"
        .ButtonName = "Select"
        If .Show <> -1 Then
            Exit Sub
        End If
        
        n = .SelectedItems.Count
        
        For Each c In rng
            c.AddComment
            Set cmt = c.Comment
            If Not cmt Is Nothing Then
            strPic = .SelectedItems(i)
                With cmt.Shape
                    .Height = 162
                    .Width = 296
                    .Fill.UserPicture strPic
                End With
            End If
            i = i + 1
            If i = n + 1 Then
                Exit Sub
            End If
       Next c
       
    End With
End Sub

 

[karthikkarthik]

HI ipe3b,
Can you please help
can i insert the images for file path, not web URL, but local folder file path in selected range to selected destination range ?
Thanks