Pasting Pictures into a Comment

Andres has a picture in the Clipboard. He would like, for the selected cell, to place the picture into a comment. If the cell has no comment, then one would need to be created. If there is a comment already, then the picture would need to be added to it. Andres knows this would take a macro, but he's not sure how to work with comments in a macro to do what he needs.

Microsoft, in the latest Office 365 version, now refers to comments as notes. For the purposes of this tip, however, I'll continue to refer to them by the traditional comments name.

What Andres wants to do is nowhere near as easy as one might desire. The first problem is that there seems to be no way in VBA to use the Clipboard as the source of an image destined for a comment. It is possible to get around this by changing the source to be an image in a file—in other words, to have the macro allow the user to select an image file that is then placed in the comment.

Sub AddCommentPicture()
    Dim PicChoice As Variant

    If ActiveCell.Comment Is Nothing Then
        ActiveCell.AddComment
    End If
    PicChoice = Application.GetOpenFilename("JPEGs *.jpg,*.jpg")

    If PicChoice = False Then
        MsgBox "No file was selected."
    Else
        ActiveCell.Comment.Shape.Fill.UserPicture PicChoice
        ActiveCell.Comment.Shape.LockAspectRatio = True
    End If
End Sub

The macro tests the active cell to see if it has a comment. If not, it will add one. It then displays an Open dialog box that shows only JPG files. (You can change the GetOpenFilename function's parameter to indicate what types of files should be displayed.) The file you pick is then assigned to the comment.

Note that the code does nothing to resize the image. You can, if desired, add the code necessary to do the resizing. You'll want to add that code directly after the line that locks the aspect ratio of the image, near the end of the macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5489) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.