How to Display Pictures on Excel Worksheet Using VBA


A user wishes to display pictures on an Excel worksheet using VBA of his employees or inventory. He enters the name of an employee or an item, clicks on a command button and voila a picture is displayed.

So the first thing we do is tell the user where to enter the name of the employee or the inventory item, for example, in cell A2. Next we create a command button ActiveX control on the Excel worksheet. In the design mode we double click the command button and write our code. Our aim is to be able to display the correct picture. For this, our code likes so:

Private Sub cmdDisplayPhoto_Click()
Application.ScreenUpdating = False

Dim EmployeeName As String, T As String

myDir = “C:\Users\takyar\Pictures\employees\”
EmployeeName = Range(“A2”)
T = “.jpg”
Range(“C10”).Value = EmployeeName
ActiveSheet.Shapes.AddPicture Filename:=myDir & EmployeeName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120

Application.ScreenUpdating = True

End Sub

Watch the training video to learn how we can display pictures on Excel worksheet with VBA and then study the VBA code in greater detail:


What-if we write a name of an employee or an item name incorrectly or the item/name does not exist at all?
We need to do a error-handling that tells the user in a friendly manner that the file does not exist.

So now the VBA code of our command button looks like so:
Private Sub cmdDisplayPhoto_Click()
Application.ScreenUpdating = False
myDir = “C:\Users\takyar\Pictures\employees\”
EmployeeName = Range(“A2”)
T = “.jpg”

Range(“C10”).Value = EmployeeName
On Error GoTo errormessage:

ActiveSheet.Shapes.AddPicture Filename:=myDir & EmployeeName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120

errormessage:
If Err.Number = 1004 Then
MsgBox “File does not exist.” & vbCrLf & “Check the name of the employee!”
Range(“A2”).Value = “”
Range(“C10”).Value = “”
End If
Application.ScreenUpdating = True

End Sub

If you now run the VBA code by clicking on the command button you’ll notice that many pictures start existing above each other because as we write more names the correct picture is displayed above the earlier display. So now what we need to do is to clear the earlier pictures and display only the picture whose data we wish to see.

Our final VBA code now looks like so:

Private Sub cmdDisplayPhoto_Click()
Application.ScreenUpdating = False
Dim myObj
Dim Pictur
Set myObj = ActiveSheet.DrawingObjects
For Each Pictur In myObj
If Left(Pictur.Name, 7) = “Picture” Then
Pictur.Select
Pictur.Delete
End If
Next

Dim EmployeeName As String, T As String

myDir = “C:\Users\takyar\Pictures\employees\”
EmployeeName = Range(“A2”)
T = “.jpg”

Range(“C10”).Value = EmployeeName
On Error GoTo errormessage:
ActiveSheet.Shapes.AddPicture Filename:=myDir & EmployeeName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=190, Top:=10, Width:=120, Height:=120

errormessage:
If Err.Number = 1004 Then
MsgBox “File does not exist.” & vbCrLf & “Check the name of the employee!”
Range(“A2”).Value = “”
Range(“C10”).Value = “”
End If
Application.ScreenUpdating = True
End Sub

Download sample file by clicking on the Excel icon:

Further reading:
Shapes.AddPicture Method (Excel)
View the video on YouTube