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
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,
Why would you work in Excel in a disorganized manner?
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
This link might be useful: http://www.exceltrainingvideos.com/how-to-compare-and-merge-multiple-workbooks-to-create-a-master-file/
THANK YOU SIR YOUR TRANING WILL GIVE ME GREAT HELP IN MY JOB…GOD BLESS U
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.
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
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
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
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
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
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
how can I make this code work for a range of cells such as A2:A7 ?
how can I make the loop
regards