I have a sheet with employees numbers and names and I wanted to see the corresponding photo of each employee. I have a folder with the photos of each employee. Each filename is the employee's number. To be able to see the photos, I decided to put each photo inside a comment on each employee number cell. This way, when I pass over the employee number, a comment window will show up with the employee's photo. It will look like this:
For this, I have the following code:Sub InsertPictures()
Dim cll As Range
Dim rng As Range
Dim strPath As String
strPath = "D:\Photo Folder"
With Sheets("Sheet1")
Set rng = Range("A2:A416")
End With
For Each cll In rng
If Dir$(strPath & "\" & cll.Value & ".jpg") <> "" Then
With cll
.ClearComments
.AddComment ("")
.Comment.Shape.Fill.UserPicture (strPath & "\" & cll.Value & ".jpg")
.Comment.Shape.Height = 160
.Comment.Shape.Width = 120
.Comment.Shape.LockAspectRatio = msoTrue
End With
End If
Next cll
End Sub
You can adjust the size of your photos by changing the Height (160) and Width (120).
