July 23, 2014

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

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

  1. 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,

      1. 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

  2. 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.

  3. 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

  4. 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 ShopMillProcessSheetsDrawings”
    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

  5. 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

  6. 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

  7. 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

  8. Firstly, I would like to thank you for all the wonderful videos that you have made. They are very easy to understand, especially for individuals like me who has no background in programming.

    I have a dynamic dropdown list. How can I link a specific workbook to open every time I choose an item/value on the dropdown list? I.E. I have a list of clients and each client has an external workbook for me to input data.

    I would appreciate your help on this.
    Michael

  9. how can I make this code work for a range of cells such as A2:A7 ?
    how can I make the loop

    regards

  10. Hi Dinesh,

    I am wanting to create an Add In which can be used to insert pictures with a specific reference, linked to a file with the Jpegs. Could you perhaps assist with a solution or advise where to get something like this?

    Many Thanks,
    Nick

  11. I want to display picture from user form not worksheet and I want when I put name in textbox the picture display in image1 in user form
    please help me
    thanks

  12. Hi All,
    I’m looking to solve this issue.
    i have data for numbers of spare parts with full data,and I am looking to create form in excel to print spare part card contains all data with photos.
    the issue is once i created pivot table and created slicers to select the spare part,so i need solution to import photos with dedicated number once i select the number from slicer to be automatically added..
    could you support me.
    thanks in advce

  13. Hi Sir,

    I really admire your work…your the best in excel I ever seen….if you don’t mind I have a request you to make a video… If someone click of A1 cell then current date and time will come automatically on B1 cell and if someone enter anyvalue on C1 cell then then current date and time will come automatically on d1 cell….. If possible make a video for us.. thanks

  14. Hi, I really enjoyed your videos and apt reply you gave to those that ask questions.

    My question is that I want a vba code that will continue to auto increase a cell value by 1 until it finishes printing a range that I want to print.

    For example if I have a value “1” in cell “B1” I want the code to continue to change it to 2, 3, 4, repeatedly when I click a button until it reaches a value that I defined in cell “B2”, i.e. cell “B2” has “10”.

Comments are closed.