Excel VBA: Insert Picture from Directory on Cell Value Change

[@karlf3613]

I really appreciate the video. I'm new to VBA and can't figure out how to loop this macro so that a value entered into cells A2, A3, A4, etc. will have an image populate beside the corresponding cell (e.g. image with file name written in A2 shows in B2, A3 in B3, and onward). Can you help? Thanks!

[@shahzadisrani2616]

Sir thankyou very much your video is too useful but I have one query. I have +5k images, some are in png, jpg, jpeg format so kindly suggest instead of just png, how can we add multiple image extensions Thanks

[@IvyPlans]

Thi works great, how can you add multiple rows, I added a range of "A2:A10" in the VBA code, but it did not work. Do you have a video link that covers this and adding image height with an auto width, and centering of the I image into the cell. Thank you!

[@timotheebrucho4211]

Awesome job @Computergaga! Very useful, but I still have few questions : Q1 Is it possible to specify different type of pictures formats (.jpg, etc.) and not only .png ? How do you write it in the code ? Q2 Is it possible to perfectly adapt the cell to the picture size and not only the height, i.e. also the column (width) How do you write it in the code ? Thanks a lot, Tim

[@khalidhance1940]

sir,how we can insert pic from userform to excel sheet in next empty cell.

[@marnescoetser5970]

Thanks for the info. I have another question. I have a list of Cars (for Example) with some extra info tipped on Excel and that same list of cars as images in a folder. I want excel to add those images to the spreadsheet automatically in a cell. How do I go about, is there a formula to get that done? If you have any info regarding this I would really appreciate it.

[@khayamaluminium1176]

Good day! i copied and pasted these code in Module but when i run the micro the micro list doesn't show any micro can you assist please. Many Thanks

[@salahaddin2009]

Thank you. But i want to enter a name in cel a3,a4,a5 etc and the image to update in b3,b4,b5 etc.. would i manually copy those cell ranges in or is there a one line code that takes care of that? Good work.

[@Computergaga]

This code runs of the worksheet change event where the changed cell is target. This example tests target to see if it is B2. You could test if target is in column A with If target.column = 1 then. Then instead of With Range("B2") you can use With Target.Offset(0,1)

[@sarahdavid1798]

@@Computergaga would you be able to write it out? im trying to piece together what your saying, but its not working wit how im understanding what your saying to edit. :/ for example i inputted If Target.column = 1 then Target.Offset(0,1)("0,1").Address Then

[@shozibjavaid4803]

@Computergaga Sir, basically I want to take url from a cell then after downloading the picture I want the picture to be inserted in a specific cell. Could you please help me in this question as well???

[@sreejithganeasn8656]

Hai How can lookup multiple images from one excel sheet to another without using Name manager option ?? PLZ help to resolve , Iam having more hand 700 images in a excel sheet , But i need only few items picture from that on another sheet

[@IvyPlans]

How can you insert either.png or .jpg

[@brad151159]

Hi. Is there a way to just delete the picture in B2 and not all pictures on the sheet? I have other pictures on the sheet that I don't want to delete. Change- ActiveSheet.Pictures.Delete to ?

[@Computergaga]

You can delete the picture using its name such as below. There are other techniques too. ActiveSheet.Shapes("Picture 1").Delete

[@brad151159]

Hi. Thanks. I did a work around in that I still delete all images and just bring my logo an the changing image in each time. Thank.

[@macd799]

@@Computergaga To clarify, can "ActiveSheet.Pictures.Delete" be modified to delete from a specific cell/range [and not all images]? Ie: .... If Target.Address = Range("A2").Address Then ??Range("B2")??.Pictures.Delete PictureLoc = "***" & Range("A2").Value & ".png" With Range("B2") ... This method is giving me much faster results (than kzbin.info/www/bejne/npK9oX2ega-VmqM) but I'm a true VBA novice. Following other methods to delete from a specific range require a different Sub, and I'm hoping to be able to make a simple change to your method. Any help is much appreciated.

[@macd799]

@Computergaga I think I've been able to answer my own question by (learning how to) calling another Sub - replacing "ActiveSheet.Pictures.Delete" with "Call DeleteImage" With this additional Sub: Sub DeleteImage() Dim pic As Picture ActiveSheet.Unprotect For Each pic In ActiveSheet.Pictures If Not Application.Intersect(pic.TopLeftCell, Range("B2")) Is Nothing Then pic.Delete End If Next pic End Sub ------------------- Many thanks again for your video

[@macd799]

Final comment, I have found this to be a more reliable way of deleting an image from selected cells. Sub DeletePic() Dim xPicRg As Range Dim xPic As Picture Dim xRg As Range Application.ScreenUpdating = False Set xRg = Range("B2") For Each xPic In ActiveSheet.Pictures Set xPicRg = Range(xPic.TopLeftCell.Address & ":" & xPic.BottomRightCell.Address) If Not Intersect(xRg, xPicRg) Is Nothing Then xPic.Delete Next Application.ScreenUpdating = True End Sub

[@kentaczdys]

Hi. Great video. I have a problem like "iCode 1011" I need to have a pictures in columns D,E,F,G,H - row 3 based on dropdown list from row 4. I have copied and paste the code but it doesn't work. I tried to understand the other suggestion of yours ("This code runs of the worksheet change event where the changed cell is target. This example tests target to see if it is B2. You could test if target is in column A with If target.column = 1 then. Then instead of With Range("B2") you can use With Target.Offset(0,1)") but have no idea how it works. Will you be able to explain that in example. Thanks.

[@anubis9139]

I'm getting a runtime error 1004 on the line where you "Set myPict = ..."

[@FWWeitzel]

same for me

[@sachin0200]

sir, how to use this code in command button (add a picture in particular cell), kindly reply...

[@Spiderjolly]

Will the photo be visible after emailing it to someone using a macro to send via lotus notes ?

[@Computergaga]

Yes should be. The photo is embedded into the worksheet.

[@luciasolari872]

Hello, how do i select the cells i want the picture to appear in?

[@Computergaga]

This was specified in the With Range("B2") statement. You would need to use this section to specify the cell. how you do this depends on your specific scenario.

[@samrashahzad1541]

can we insert picture in visible cell only vba? if yes, would u like to share code

[@Jensie_Pensie]

How do I apply this to multiple cells? For example, I want values of A2 to D2 and A3 to D3 and A4 to D4... etc...

[@Computergaga]

The same code used here to insert the picture to a cell could be wrapped in a loop to insert pictures in D2, D3, D4 etc using the names from column A. It can also be run from a button instead of the worksheet change event demonstrated in the video.

[@IvyPlans]

When you say wrap in a loop do you mean to repeat the code again after the "end with" statement?

[@_rcs]

Thanks for the video. The .Top and .Left properties were exactly what I was looking for.

[@Computergaga]

You're welcome Rory.

[@Seth6000]

And if I dont want the pictures to be in my desktop what can I do? I want them in Excel and insert them to a form How can I do that?

[@isabeljordan7475]

Hi! I keep getting a Run-time error '1004': "Unable to get the Insert property of the Pictures class" error. Can you please help?

[@MoobeVideos]

It is because you send the excel to someone or you dont have photos in PC anymore. The photos must be at the exact place, where you wrote it. For example "C:\\Users\Admin\Desktop" they have to be here all the time.

[@yaderaguilar814]

Works like a charm!!! But an you add a code where if you don't have a name for an item just to send a message saying the item does not exist instead of sending you to debug the code. Thank you. Appreciate your help.

[@Computergaga]

Sure, in this example a Data Validation list is used. So that feature should prevent the user from selecting something that we do not have. We could click Data tab > Data Validation > Error Alert tab and customise the message displayed to the user there. If we were not using DV we could add lines like below to test something was selected before the codes runs to test the cell first and exit the macro with a message. If Target.Address = Range("A2").Address And Target.value = "" Then MsgBox "You have not chosen an item" Exit Sub End If To test if an item exists we would need to loop through a list of all the items to check (That is why Data Validation simplifies this task).

[@scientist100]

My approach: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myPict As Picture Dim PictureLoc As String If Target.Address = Range("A2").Address Then ActiveSheet.Pictures.Delete PictureLoc = "C:\\Users\Admin\Desktop\images\" & Range("A2").Value & ".jpg" With Range("B2") On Error GoTo fbi Set myPict = ActiveSheet.Pictures.Insert(PictureLoc) .RowHeight = myPict.Height myPict.Top = .Top myPict.Left = .Left myPict.Placement = xlMoveAndSize fbi: MsgBox "Picture not found, please verify source in " & PictureLoc, vbOKOnly, "Image not found" End With End If End Sub

[@roberttrump2109]

@@scientist100 Dude THANK YOU. I was ready to pull my hair out trying to figure out how to have a blank cell without having it shut down on me.

[@macolloyd3242]

Many thanks for your idea

[@Computergaga]

You're welcome.

[@ΙωάννηςΠοντίκης-ι4θ]

Dear Sir, Thank you so muchhhh for this video!! My only problem is that I'm not using drop down list to manually change value as my reference cell is updated by a formula, so picture is not changing automatically,. Is there any way I can solve this ? Once again thank you so much for all the previous work.

[@Computergaga]

Thank you. I don't know how this can be done with a formula updating a cell though.

[@macd799]

@@Computergaga I hope this is not too late to be useful. I've been able to use the Worksheet_Calculate Sub to have the image change when there is a change in calculation for the referenced cell: --------------- Private Sub Worksheet_Calculate() Dim Xrg As Range Set Xrg = Range("A2") 'change in ? cells If Not Intersect(Xrg, Range("A2")) Is Nothing Then 'change in ? cells Dim myPict As Picture Dim PictureLoc As String ActiveSheet.Pictures.Delete PictureLoc = "***" & Range("A2").Value & ".png" With Range("B2") Set myPict = ActiveSheet.Pictures.Insert(PictureLoc) .RowHeight = myPict.Height myPict.Top = .Top myPict.Left = .Left myPict.Placement = xlMoveAndSize End With End If End Sub

[@OblisGR]

Can anyone help me do that with more than one pictures? I dont know very much of vb

[@IvyPlans]

I love this code, just one thing; I am coding on a MAC and it does not recognize the file path. I have tried writing the path in different methods based on online forums but I still cannot get the right code. How should I write the code for a MAC?

[@Computergaga]

I do not now about the differences between macs and Windows here. I am not a mac user.

[@IvyPlans]

That's okay, thanks for getting back to me so quickly.

[@RajendraSharma-jg1oo]

formula is good but one issue as image is insert i want to insert images of specific dimension can i get from this as i am getting large big size picture which i have to set it ?????

[@Computergaga]

Sure. You can specify the height and width of the picture on insertion. You can use the properties; .Height = 200 .Width = 300 to specify these dimensions. You can also disable/enable the lock aspect ration of an image with this line .ShapeRange.LockAspectRatio = msoTrue or .ShapeRange.LockAspectRatio = msoFalse

[@RajendraSharma-jg1oo]

Computergaga thanks a lot its working

[@Computergaga]

Great to hear Rajendra.

[@brad151159]

Hi. Really appreciate this video. Is there a way to just specify the images directory relative to the directory that the spreadsheet is saved in? For instance, an images directory directly beneath the directory that the spreadsheet is saved in. Instead of - PictureLoc = "C:\\Users\Admin\Desktop\images\" & Range("A2").Value & ".png" Something like PictureLoc = "..\images\" & Range("A2").Value & ".png" Thanks in advance

[@Computergaga]

I am not sure if you can do exactly what you ask. I would need to test it. But we can definitely use a workaround. For example, to return the file path of a workbook you can use something like ActiveWorkbook.Path. So if this was assigned to a variable such as wbkPath = ActiveWorkbook.Path And then PictureLoc = wbkPath & "\images\" & Range("A2").Value & ".png" This would give the impression of looking relative to the spreadsheet location.

[@brad151159]

Hi. Thanks for taking the time to reply. It worked just as you indicated. Brilliant.

[@IvyPlans]

iCode 1011 Hi I would love to be able to loop this so that I can make it repeat until the end of the column, for example I have 10 rows, can you please help with looping, I have tried multiple times but get an error when writing the code., I am using a PC.

[@gokmenslbas1627]

Here is my version. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, [e:e]) Is Nothing Then Exit Sub On Error GoTo exit: 'here we clean pictures in a specific range not the whole worksheet For Each Pic In ActiveSheet.Pictures If Not Intersect(Pic.TopLeftCell, Range("A1:j60")) Is Nothing Then Pic.Delete End If Next Pic Dim pictureLoc As String Dim mypict As Object For i = 18 To 45 pictureLoc = "E:\TEKLİF\Teklifler\Resim" & "\" & Range("e" & i) & ".jpg" Set mypict = ActiveSheet.Pictures.Insert(pictureLoc) With Range("c" & i) mypict.Top = .Top mypict.Left = .Left mypict.Height = .Height mypict.Width = .Width End With Next i exit: End Sub

[@patjosullivan1]

Could you add error code to handle a situation where there are no pictures for the name. Also, I would like to get a unique look up value from a combination of 3 cells, Concatenate or & functions will not work. Other than that it works perfectly, cheers.

[@Computergaga]

Sure. I believe the Runtime error 1004 is displayed if it cannot find the picture. This will occur when i tries to insert it. You could add an On Error statement to handle this error code. The concatenation can be done with the ampersand. For example; PictureName = Range("A2").Value & Range("B2").Value & Range("C2").Value

[@patjosullivan1]

Cool, got the concatenate working, just trying to figure out the error code now, cheers.

[@Computergaga]

Good to hear. Yes, Google the On Error statement. You can test if Err.Number = 1004 and perform an action.

[@renesaccardo]

How can i do this on PowerPoint?

[@MohAboAbdo]

Thank you very much for this great video, for this unique code, and for this great explanation.

[@Computergaga]

You're very welcome. Thank you.

[@najmulgani2814]

IT IS POSSIBLE TO TYPE THE FILE NAME IN EXCEL AND INSERT PICTURE IN ANOTHER CELLL

[@antonrodr478]

IS there any way to give dimensions to image from cell

[@Computergaga]

Sure, this can also be added to the With construct with .Width = ? and .Height = ?.

[@shabbirj]

@@Computergaga hi kindly send some example of fix width and height

[@shozibjavaid4803]

@ Computergaga I am having this error, 1004 unable to get the insert property of the picture class. Could you please tell me, how to tackle it?

[@Computergaga]

There is an error somewhere in your picture name, extension or file path.

[@shozibjavaid4803]

Private Sub Worksheet_Change(ByVal Target As Range) Dim myPict As Picture Dim PictureLoc As String If Target.Address = Range("A2").Address Then ActiveSheet.Pictures.Delete With Range("B2") Set myPict = ActiveSheet.Pictures.Insert("C:\\Users\Admin\Desktop\images\" & Range("A2").Value & ".jpg") .RowHeight = myPict.Height myPict.Top = .Top myPict.Left = .Left myPict.Placement = xlMoveAndSize End With End If End Sub

[@shozibjavaid4803]

This is the code I am using, can you sp0t the error please?

[@Computergaga]

I don't think the error is necessarily with the code Shozib. The code uses the C:\\Users\Admin\Desktop\images\ path. Is that the path your image is saved under? It uses a jpg extension. Is your image a jpg? or is it .png or .bmp or. gif? Also check cell A2. Is it spelt correctly in the cell and the image name?

[@shozibjavaid4803]

("C:\\Users\Admin\Desktop\images\" & Range("A2").Value & ".jpg") This is the path which I am using

[@deepalparekh3217]

Hi, I am unable to get the result for multiple cells, can you help me with the code

[@Computergaga]

You would need the target check to look at the cells in a range, rather than just B2 like the video. You probably want a variable for the row number. How familiar are you with VBA? What do you have so far?

[@mehboob1976]

how to do this in google sheet?

[@Computergaga]

Not sure. I think Google Sheets has a formula for this. No VBA.

[@sasitanukucollege6739]

sir send me sample sheet images sir no of time try not showing iamges sir

[@hasmuddinratnani5561]

yes can you help with the file

[@tomkei7374]

Could you please sir tell me how do I insert two pictures at a time in two cells by giving a name

[@shahabali4518]

It is simple take double picture dim and strings and it works

[@mikeygrey9855]

@@shahabali4518 Can you share your code bro?

[@estelam905]

I've tried the code, and it didn't work for me. I've copied and pasted the code exactly as yours, only changed the following "C:\\Users\Admin\Desktop\images\" to my own path. When I tried to use the drop down list it gives me the following error "Run-time error '1004': Unable to get the Insert Property of the Picture class" When I press "debug" it highlights the following in yellow "Set myPict = ActiveSheet.Pictures.Insert(PictureLoc)" Please help, if you could please send me the file on my email or let me know how I can correct it, it would be greatly appreciated.

[@Computergaga]

Is your worksheet protected? Also check that you are using a variable named PictureLoc and that you have used a valid file path with the appropriate slashes \

[@estelam905]

Thanks a lot for your prompt reply, I've been racking my brain trying to figure out how to do what you showed on your video and have been unable to find another way which uses a cell with drop down list. In response to your questions: 1. No, my worksheet is not protected 2. I copied the code exactly as yours, the only change I made was to the file path - I've pasted it below to see if you can somehow help me figure it out why it gives me the error I stated earlier Private Sub Worksheet_Change(ByVal Target As Range) Dim myPict As Picture Dim PictureLoc As String If Target.Address = Range("A2").Address Then ActiveSheet.Pictures.Delete PictureLoc = "C:\\Users\henry\Desktop\images\" & Range("A2").Value & ".png" With Range("B2") Set myPict = ActiveSheet.Pictures.Insert(PictureLoc) .RowHeight = myPict.Height myPict.Top = .Top myPict.Left = .Left myPict.Placement = xlMoveAndSize End With End If End Sub

[@Computergaga]

My next guess would be to check the name and file format of your picture. Maybe it is not a png file. If not this I'm out of ideas for the moment.

[@estelam905]

Thanks for all your help. Would you be willing to send me the file with the code to my email? I've tried everything and have been unable to make it work. It gives me the same error I sent you above. The only VBA code I was able to make work that is similar to yours is the following, but the huge drawback is that it does not allow "A2" to have a drop down list. I am really new to coding, so have been unable to figure out how to make the code below accept a drop down list. Private Sub Worksheet_Change(ByVal Target As Range) Dim shp As Shape If Intersect(Target, [A2]) Is Nothing Then Exit Sub If Target.Row Mod 20 = 0 Then Exit Sub On Error GoTo son For Each shp In ActiveSheet.Shapes If shp.Type = msoPicture And shp.TopLeftCell.Address = Target.Offset(0, 4).Address Then shp.Delete Next If Target.Value "" And Dir(ThisWorkbook.Path & "\" & Target.Value & ".jpg") = "" Then 'picture not there! MsgBox Target.Value & " Doesn't exist!" End If ActiveSheet.Pictures.Insert(ThisWorkbook.Path & "\" & Target.Value & ".jpg").Select Selection.Top = Target.Offset(0, 4).Top Selection.Left = Target.Offset(0, 4).Left With Selection.ShapeRange .LockAspectRatio = msoFalse .Height = Target.Offset(0, 4).Height .Width = Target.Offset(0, 4).Width End With Target.Offset(1, 0).Select son: End Sub

[@Computergaga]

The drop down list is created with Data Validation. You can find this on Data to create a simple drop down list.

[@sureshatsasi]

Sir vba code Not working so plz sample zip file send me sir

[@stevewesslersr7014]

The fORMULA DID NOT WORK FOR ME., i EVEN CHANGED ALL MY PICTURES AND NAMES TO WHAT YOU SHOW IN THE FILE. THIS IS THE ERROR i GET. With Range("B2") Set myPict = ActiveSheet.Pictures.Insert(PictureLoc) THE CODE LINE ABOVE IS HIGHLIGHTED IN YELLOW. WITH THIS ERROR UNABLE TO GET THE INSERT PROPERTY OF THE PICTURE CLASS

[@Computergaga]

I would check the format of your pictures. Are they all JPG? May have to change the code, or the picture formats to fit.

[@soulquest85]

@@Computergaga Hi, if I want to insert JPG, do I just change the .png to .jpg?

[@SanthoshKumar-tr2rn]

Hi Thank you for your Coding, I am looking for a help in my following code, if the photo is not in my folder the Cursor is not coming back to my default Cell (B2), but if the photo is there, it will back to B2, how i will rectify the issue Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Sheet1.Unprotect Password:="123" On Error GoTo finish Dim myPict As Picture Dim PictureLoc As String If Target.Address = Range("B2").Address Then ActiveSheet.Pictures.Delete PictureLoc = "C:\MASTER DATA\STAFFPHOTO\" & Range("B2").Value & ".jpg" With Range("C2") Set myPict = ActiveSheet.Pictures.Insert(PictureLoc) myPict.Top = .Top myPict.Left = .Left Sheet1.Protect Password:="123" End With ActiveSheet.Protect.EnableSelection = Range("B2").Activate End If finish: End Sub

[@nelsondarwinpaktech3954]

great

[@Computergaga]

Thank you Nelson.

[@tutspamore8419]

Now i got the idea

[@Computergaga]

Excellent! Happy to help.

[@jorgehumbertodossantos6612]

NIce. 10

[@Computergaga]

Thank you, Jorge.

[@PhantomMonk3y]

how do i add more pics to the next cell below?