Excel VBA: Inserting Photos as Comment in Bulk

Date:2020-11-4 Author:Sandra

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.

Excel VBA:  Inserting Photos as Comment in Bulk

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.

Excel VBA:  Inserting Photos as Comment in Bulk

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

Excel VBA:  Inserting Photos as Comment in Bulk

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.

Excel VBA:  Inserting Photos as Comment in Bulk

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.

Excel VBA:  Inserting Photos as Comment in Bulk

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

Excel VBA:  Inserting Photos as Comment in Bulk

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

Excel VBA:  Inserting Photos as Comment in Bulk

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

Excel VBA:  Inserting Photos as Comment in Bulk

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

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *