Mr Excel & excelisfun Trick 42: Lookup Picture In Excel Formula or VBA?

[RoderickFred]

With a small change I FINALY got this to work for my self. It may be opperator error but I changed Bill's MR. EXCEL code to this and got it to work:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then NewPic = "C:\aaa\pics\" & Range("c7").Value Target.Comment.Shape.Fill.User­Picture PictureFile:=NewPic End If

End Sub

[Sherlock69Holmes]

Mr Excel , Have Implemented the excel trick and it worked as shown . But when i close , got a pop-up to save as excel Macro enabled workbook . so i did save as Book.xlsm format.

when Opened , still i can see VB logic , all details , but picture does not change.

Please help me , do i need to take care of any other thing as part of trick.

[Sherlock69Holmes]

hey Mr Excel.., am not getting words for how should i appreciate . Its awesome and thanks a ton for sharing these tricks. found these sessions , extremely helpful and customized as per my requirement and completed a small project on it .

Thanks Again Mr Excel

[ExcelIsFun]

@Sherlock69Holmes , you are welcome!

[tomboi1978]

Is the reason the second trick doesn't work in excel 2007 because you can't add a formula to the picture itself? I was doing this in excel 2010 for a while and only found out excel 2007 wouldn't do it when I tried to create a new sheet for a co-worker. I got stopped when trying to enter the formula into the picture. It wouldn't let me type anything in the formula bar with the pic selected. If I opened the 2010 version in 2007 it worked. I just couldn't create a new one from scratch.

[larrym14]

I found a link to this by doing a google lookup using this as the lookup value: mrexcelexcelisfuntrick42 filetype:xls

[ExcelIsFun]

@larrym14 , that is amazing! However, I thought Google stopped supporting GoogleLookup as of nov 2011...

[dsnhira]

EXTREEEEEEMMMMMLYYYYYYYYYY AWESOMEEE..... Thank you & mike to make the so nice one. Actually Ive come to learn mike's formula one but VB is also fun and added in my brain as a bonus item. Thanks again.

[ExcelIsFun]

You are welcome!

[reddcannon]

@ExcelIsFun I cannot get this to work in excel 2007. I did it exactly as you, description changes in cell c7, but picture does not change

[nso555]

Hi, first i want to thank you for excellent video, i learned a lot from your videos, but i didn't understand well some moments from your video.

1.You pasted a long command in in sheet 1, which i didn't recognize from where? it confused me. can you explain me that moment???

2. Does NewPic command exist in Excel??? it also confuses me ((

i hope my questions are clear to understand

waiting for reply

thanks in advance

[ExcelIsFun]

do not know how to help you with VBA. Try e-mailing Bill at:

pub at mrexcel[dot]com

Bill Mr Excel Jelen is the guy who did the VBA in the video.

[ExcelIsFun]

I do not know how to help you with VBA. Try e-mailing Bill at:

pub at mrexcel[dot]com

Bill Mr Excel Jelen is the guy who did the VBA in the video.

[12345thatsit]

I was desperately looking for a means by which I could look up an image in excel. When all hope seemed lost I found your video. Many thanks for sharing your knowledge & hope to learn more from you.

[ExcelIsFun]

I am glad the video helped!!

[karzuda]

thanks , it's helpful

[ExcelIsFun]

I am glad that it helped!

[MizzouAP]

I am continually getting a "cannot find specified file" message. I have checked my linking route over and over again and it seems to be written correctly. Do you have any tips I may be overlooking?

[eddanek]

Thanks. Helped a ton. (used comments in Excel 2007)

[ExcelIsFun]

I am glad that it helped!

[MizzouAP]

@ExcelIsFun When I try the Comment Box method I continually get a "Specified File Cannot be Found" message. I checked the path I gave it a hundred times but couldn't find anything wrong with it. The line highlighted when I go to debug is the "Target.Comment.Shape....". Do you have any tips for me that might help? You do awesome work and help me out a ton! Thanks a lot!

[ExcelIsFun]

I do not know how to help you with VBA. Try e-mailing Bill at:

pub at mrexcel[dot]com

Bill Mr Excel Jelen is the guy who did the VBA in the video.

[RaJz92]

I like this video. I find it very useful!

[ExcelIsFun]

I am glad that it is helpful!

[Nandy1986ify]

Super

[ExcelIsFun]

I am glad that you liked it!

[Nandy1986ify]

Hi, i really love this, it does help a lot.

[Nandy1986ify]

Super

[ExcelIsFun]

I am glad that you like it!

[kelch12]

Fantastic Excel trick here.

I personally love to deal with dynamic graphic in excel. I would have thought "Picture Link" would do this trick but it turned out my "Picture Link" idea didn't capture the image overlaying on the reference cell.

The "Excelisfun" version does the effect that I'm looking for. It is straight forward and easily re-apply without remember a VBA code. I think I can apply this trick to my future use :)

[kelch12]

The "Mr. Excel" version looks simple in the surface but a little bit of VBA involved. I like how the image files are not "hard coded" in the excel file so the user could replaced the picture outside of excel and keep the .XLS file small. However, this technique required "Comment" and the reference cells are absolute in the VBA code. That limits the flexibility to move the picture around,

Overall, very clear tutorial for both Mr. Excel and Excelisfun. I really like this one A LOT!

[ExcelIsFun]

EXCELlent!!!!

[hyunnh]

this is great pod cast! love you guys!

[ExcelIsFun]

Thanks!

[farouk450]

Dear Sir

I have tried it it works with VBA , But when I tried the formula method, I can't adjust the name range to use LOOKUPPICTURE i have even tried to write it by hand but it doesn't works while it works normally in your file.

My range name is sheet! and the cell name not the look up picture,

Can you help me to know what is my mistake?

Thanks in advance

[ExcelIsFun]

I don't know how to help. I would try it again in a new file and do the steps just as you see in the video.

[farouk450]

I have only one mysterious thing , if you open the name manager in your file your will find the range starts with lookuppicture

But when I used the Creat name from selection , my range starts with sheet

[ExcelIsFun]

I do not understand your question. Can you re-state it?

[ExcelIsFun]

I saw your video. You just need to name your sheet. Your sheet name is Shaeet1, my sheet name is LookupPicture.

That should do it!

[farouk450]

BINGO..........

I did it

Thanks Thanks Thanks

It is great

[ExcelIsFun]

EXCELlent!!!!!

[Sherlock69Holmes]

@farouk450

Hi , does this VBA worked , it worked for me exactly as shown . but when i closed it and opened again , picture does not change dynamically with the name.

what format does i need to save my file ?? please help

[ExcelIsFun]

@Sherlock69Holmes , I am excelisfun, not Mr Excel. Mr Excel did the VBA. I am not so good with VBA. However, it should work in a workbook with the extension .xlsm

[LotfyKozman]

Awesome. Is it MS Excel or MS Access

[ExcelIsFun]

Excel.

[harmeet1421]

killing awesome............

[ExcelIsFun]

Yes!!!!

[harmeet1421]

i mean u guys r awesome........... :)

[ExcelIsFun]

Thanks!

[thegreatmonster]

this is a cool trick..

[ExcelIsFun]

I agree! Thanks to Excel Net Tips and Mr Excel!!

[robzrob]

Too fast! Format Comment - What's the dots/diagonal lines thing - I can't see what you did nor find whatever it was.

[ExcelIsFun]

Microsoft made this way too hard! But to format a comment, you have to click on the outside edge - if the outside edge of the comment shows diagonal lines you cannot format the comment (or add picture) - if the outside edge of the comment shows dots you CAN format the comment.

If you watch that part again, I think you will see it!

--excelisfun

[harmeet1421]

@robzrob u hav option oh pausing the video........dont u

[robzrob]

I did, but still couldn't work it out!! But Mr E helped and I've got it now :)

[harmeet1421]

gud......isnt mr excel rock..... :)

[robzrob]

Pure joy!