X

Excel VBA: Inserting Photos as Comment in Bulk

When we use Excel to make some product price lists or personnel lists, we will add a picture to the cell behind the name to give a hint. Will you insert these pictures one by one, and adjust the size. Today, the author will share with you guys a perfect method which can insert photo comments in bulk.

Let’s take a look at the renderings first.

Does this look cool? Next, the author will introduce to you in detail how this is done.

Step1: You need to store the folder which containing the pictures in the same folder as the Excel table.

Step2: Right-click on the sheet and select View Code. Copy the following code into the box.

SubInsertPicturesAsCommentInBulk

Dim a

a = MsgBox(Remembertousethemousetoselectthecellsthatneedsimagecomment)

If a = 1 Then

On Error Resume Next

Dim MR As Range

Dim Pics As String

For Each MR In Selection

If Not IsEmpty(MR) Then

MR.Select

MR.AddComment

MR.Comment.Visible = False

MR.Comment.Text Text:=””

MR.Comment.Shape.Fill.UserPicture PictureFile:=ActiveWorkbook.Path & “\imges\” & MR.Value & “.jpg”

End If

Next

End If

End Sub

Step3: Remember to use the mouse to select the cells where you want to insert the pictures as comments before running the code.

If you did not complete it successfully, you need to pay attention to a few very error-prone areas.

1. The Excel file needs to be stored in the same folder as the folder containing the pictures.

2.The name of the folder which contains pictures should be the same as the code content.

3. The cell content needs to be the same as the corresponding image name.

4. The image format also needs to be consistent with the code content.

Is this method very practical and efficient? There are many other tricks that you should not miss.

Categories: Excel Tips
Sandra: