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

11 thoughts on “How to Display Pictures on Excel Worksheet Using VBA

  1. B P RAO

    Dear Sir,
    I am having two excel workbooks. One is organized one with Column headers like, Serial Number, Name of the employee, Date of Birth, Gender, father’s name, Date of Joining. The other excel workbook is also having the same column headers in disorganized way like, date of birth, father’s name, serial number, name of the employee, gender, date of joining and same additional particulars.
    What I want to do is copy the data from the second workbook to the first workbook after exiting data (i.e., next available empty row). I may also have the option to change the column headers in the first workbook or reorganize the column headers of the first workbook. Please help me with a VBA solution.
    Thanking You,

    Reply
      1. B P RAO

        Dear Shri Takyar,

        We have two kinds of field staff working. Directly working under us and working under our agents. When We ask them to collect some information each field staff enters the data in excel in his own style. When I try to merge the data I face the above problem and need for reorganization..

        with regards

        Reply
  2. Leow.T.C

    Dear Sir,

    Thank you for your video on showing how to link employees pictures with VBA.I am not familiar with the VBA CODE.
    I have an error messages when I follow the instructions on your video.
    There is run 448.named argument.
    can you please help me.

    Reply
  3. Leow.T.C

    Dear Sir,

    I me again, thank you very much, I found the error is actually from the activesheets.Addpicture Filename…….. I didn’t place the correct name of the picture folder.
    Thanks a lot.

    Leow.TC

    Reply
  4. Eugene

    Dear Sir,

    I Used your example but i keep getting a

    Run-time error ‘424’:
    Object required

    error at the T = “.pdf” line. Not sure what i did wrong.

    Here is my code-

    Private Sub cmdDisplaypdf_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 pdfName As String, T As String

    MyDir = “G:\Machine Shop\MillProcessSheets\Drawings\”
    pdfName = Range(“O7”)
    T = “.pdf”

    Range(“O9).Value = pdfName
    On Error GoTo errormessage:
    ActiveSheet.Shapes.AddPicture Filename:=MyDir & pdfName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=704, Top:=1005, Width:=120, Height:=120

    errormessage:
    If Err.Number = 1004 Then
    MsgBox “File does not exist.” & vbCrLf & ” Check name!”
    End If
    Application.ScreenUpdating = True
    End Sub

    Thanks in advance,
    Eugene

    Reply
  5. naizma

    hello sir, thanks for this notes i want to know how to do inventory system in the same way along with . picutres pls help me sir

    Reply
  6. Victor Gameiro

    I am running VBA with Excell 2016 for MAC.

    The method you used to insert pictures does not seem to work on the MAC software. I have different ways to select and filename as follows:
    RootFolder = “Macintosh HD:Users:Victor.Gameiro:Documents:Pictures:”
    strPictureFilePath = “Macintosh HD:Users:Victor.Gameiro:Documents:Pictures:”
    strPictureFileName = Range(“k2”).Value
    Picturename = Range(“k2”).Value

    To insert the actual picture I have also tried several methods as follows:

    ActiveSheet.Pictures.Insert(Trim(RootFolder & Picturename & “.jpg”)).Select

    ActiveSheet.Shapes.ADDPICTURE Filename = RootFolder & strPictureFileName & _
    “.jpg”, False, True, 5, 20, 200, 120

    ActiveSheet.Shapes.ADDPICTURE Filename = strPictureFilePath & strPictureFileName & _
    “.jpg”, msoFalse, msoTrue, 5, 20, 200, 120

    ActiveSheet.Shapes.ADDPICTURE Filename = RootFolder & Picturename & _
    “.jpg”, False, True, 5, 20, 200, 120

    Nothing seems to work. I get an error message as follows:

    Run-time error ‘1004’:
    Unable to get the Insert property of the Pictures class

    I think that the problem may be with the path. But this is the correct path except that I may be formatting it wrong.

    Thank you for your help.
    VIctor

    Reply
  7. navin

    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

    pictur is not deleted i given name to 1001.jpg

    Reply

Leave a Reply

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