How to change a picture in a worksheet dynamically [VBA]
Rahul asks:
I want to know how to create a vlookup sheet, and when we enter a name in a cell Excel shows all the details and a photo.
The formula in cell C4 uses the value in cell C2 to lookup the correct region specified in the "Data" worksheet. You can easily add more details using this approach for example in cell D4.
Formula in cell C4:
The MATCH function looks for a matching value in cell range A2:A9 in worksheet Data using condition specified in cell C2 located on worksheet "Lookup".
MATCH(C2,Data!A2:A9,0)
becomes
MATCH("Giraffe", {"Giraffe"; "Rhino"; "Elephant"; "Buffalo"; "Lion"; "Tiger"; "Hippo"; "Cheetah"}, 0)
The third argument is 0 (zero) meaning there must be an exact match or the function returns an error.
MATCH("Giraffe", {"Giraffe"; "Rhino"; "Elephant"; "Buffalo"; "Lion"; "Tiger"; "Hippo"; "Cheetah"}, 0)
returns 1. The value "Giraffe" is the first value in the array.
The INDEX function returns a value from cell range B2:B9 in worksheet Data using a row argument which we calculated in the previous step.
INDEX(Data!B2:B9,MATCH(C2,Data!A2:A9,0))
becomes
INDEX(Data!B2:B9, 1)
becomes
INDEX({"Africa"; "Africa"; "Africa, Asia"; "Africa"; "Africa, Asia"; "Africa, Asia"; "Africa"; "Africa"}, 1)
and returns "Africa" in cell C4.
The following formula in cell C6 extracts the corresponding file path and file name, however, the value is hidden with the use of cell formatting.
Formula in cell C6:
I have hidden the contents in cell C6.
- Select cell C6.
- Press Ctrl + 1 to open the cell formatting dialog box.
- Press with left mouse button on Custom
- Type ;;;
- Press with left mouse button on OK button.
Data sheet
The Data worksheet contains the item names in column A, the details in column B and the file name and file path in column C.
VBA code
'Event code
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if cell C2 has been changed
If Target.Address = "$C$2" Then
'Delete the previous image
Shapes(1).Delete
'Insert and select image specified in cell C6
Pictures.Insert(ActiveSheet.Range("C6").Value).Select
'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Selection
'Change image height to 120.24 points
.Height = 120.24
'Change image width to 180 points
.Width = 180
'Position image 96.75 points from left border of Excel worksheet
.Left = 96.75
'Position image 90 points from top border of Excel worksheet
.Top = 90
End With
'Select cell C3 of active worksheet
Range("C3").Select
End If
End Sub
The VBA code above is event code meaning it is rund if a cell value changes. Event code is put in a worksheet or a workbook module and not in a regular module, there are detailed instructions below on where to put the code.
The VBA code above is not going to work great if you have other shapes or images placed in the same worksheet.
The following line deletes the first image in the active worksheet and if you have more images the code may delete one of those images which is not ideal.
Shapes(1).Delete
The workaround is to save the image name to another cell which is then used to remove the old image next time the code is rund. This prevents multiple images being inserted on top of each other and keeps the workbook size smaller.
If there is no value in cell D6 the code will error out, to prevent that from happening I have enabled error handling while image being deleted.
'Event code
Private Sub Worksheet_Change(ByVal Target As Range)
'Check if cell C2 has been changed
If Target.Address = "$C$2" Then
'Enable error handling
On Error Resume Next
'Delete image using the name saved in cell D6
ActiveSheet.Shapes(Range("D6")).Delete
'Disable error handling
On Error GoTo 0
'Insert and select image specified in cell C6
ActiveSheet.Pictures.Insert(ActiveSheet.Range("C6").Value).Select
'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Selection
'Change image height to 120.24 points
.Height = 120.24
'Change image width to 180 points
.Width = 180
'Position image 96.75 points from left border of Excel worksheet
.Left = 96.75
'Position image 90 points from top border of Excel worksheet
.Top = 90
'Save image name to cell D6
Range("D6") = .Name
End With
'Select cell C3
Range("C3").Select
End If
End Sub
Make sure you apply cell formatting to cell D6 as well.
Where to put the VBA event code?
- Press with right mouse button on on the worksheet name.
- Press with left mouse button on "View Code".
- Paste code below to sheet module.
- Exit VB editor.
Animated image
Macro category
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Vba category
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists 1. Save invoice data - […]
Table of Contents Add Checkboxes to a Worksheet Copy selected rows based on check boxes Worksheet data VBA code Where […]
Excel categories
19 Responses to “How to change a picture in a worksheet dynamically [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar,
Concerning Change Picture VBA, I wasn't able to get this to run. Code stops at Shapes(1).Delete with Run-time error '-2147024809(80070057)': The index into the specified collection is out of bounds. I'm using Office 2013. There was some blog chatter about inserting image working in 2010 but not on 2007. Didn't find anything directly related to 2013. Do you know of a problem with this code and 2013 or should I keep digging myself?
Thanks and regards,
I ended up making the value of "C6" a hyperlink. Pressing with left mouse button on the link (which brought up the image in my browser). Then removing the link and selecting another image via "C2". Now it's working the way it should. Strange...
Leroy Jones,
I have not yet upgraded to the 2013 version. I want the Power pivot functionality.
I ended up making the value of "C6" a hyperlink. Pressing with left mouse button on the link (which brought up the image in my browser). Then removing the link and selecting another image via "C2". Now it's working the way it should. Strange...
Yes, strange. Thanks for sharing your solution.
how did you apply the picture path .. i am unable to connect .. please let me know hot to connect picture to excel file
Lokesh,
I added the picture paths to column C, data sheet.
Yes... column C data sheet, but how did you connect link to Column C with picture paths ... Where do you save the pictures ? Please advise to me . thanks.
Lokesh,
Where do you save the pictures ?
I save the pictures in the same path mentioned in column C.
For example, if picture path is C:\temp\1.jpg I save 1.jpg in folder C:\temp.
Hello Oscar,
I got the same problem with Leroy Jones. Code stops at Shapes(1).Delete with Run-time error(80070057).The picture can not show in the column C6, Is it because the picture paths are not connected? Please help.
Thanks and regards
Muzaffer,
I think it stops there because there is no picture to delete. Try this:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then On error resume next Shapes(1).Delete On error goto 0 Pictures.Insert(ActiveSheet.Range("C6").Value).Select With Selection .Height = 120.24 .Width = 180 .Left = 96.75 .Top = 90 End With Range("C3").Select End If End SubHello Mr. Oscar i like this your project and was trying to test it i did all in a same worksheet with adaptations needed in formulas the only thing that is not working is the vba code, it gives me the following error in this line --> "Pictures.Insert(ActiveSheet.Range("C6").Value).Select" telling "Run time error "1004" "is not possible to obtain the property insert of the class pictures". I really would like to get this work, please could you help? Maybe send me one working? Thanks.
Hello Oscar,
Thanks for your reply. I've tried with your new code, now there was no error pop-up,but still can not show picture in the column C6,only the text is shown(the picture path in the data sheet). Do I need to insert one picture first to the colum C6? And how to make it connect to the path in data sheet? Please help me because I really need to do something exactly like your project.
Thanks and regards
Fernandes and Muzaffer
I have attached a zip file to this post.
Vlookup-pictures.zip
Thank you so much for your help Oscar, it works perfectly now.
Boa noite Oscar, sou um grande apreciador de seu sitio.
É a primeira vez que comento, e tenho muito a agradecer por nos ajudar a resolver alguns de nossas dificuldades. e a minha dentre muitas é como visualizar uma imagem em pdf, eu inseri um objeto, porem gostaria de visualizar no mesmo processo em que visualizamos as imagem pelo procv como demonstrou na sua ultima postagem
Change picture (vba) video tutorial link......please please please
I need very argent this…
Change picture (vba) video tutorial link......please please please
I need very argent this…
Your code can't understand no work my execl sheet
1. have your video tutorials channel.....
2. change picture vba video tutorials plz
Sir I have a worksheet in that sheet several columns have txt "medical" heading g with other text in row1 but I want to index 2 row same column data in the columns contain text medical completely. Plz give me an array formula.
I need to display some pictures in every cell of a column or other different cell. If i use insert picture, the file becomes huge in size and it becomes extremely laggy. Could you please help me by giving the VBA code to display picture in a cell which picture is actually stored in a different location/folder?