Pictures in Excel Formula

[conradg]

Hello

I am trying something new. I would like to find out how to put a picture into a formula so that when: example if A1 equals a certain number then a picture will pop up on the worksheet.

So in otherwords - a picture will automaticly pop up - so I need a formula to include the picture.

If you need further explanation please let me know

Thank you
Gregg Conrad

 

[OMMBoy]

Apologies for resurrecting this thread that has not been posted to in nearly a year, but I found it while Googling how to insert pictures using a formula instead of VB.

I've read nearly every post in this thread and still can't figure out how to make it work -- maybe because my situation is a bit different?

Here's what I'm trying to do:
I'm trying to create a visual representation of chips on a wafer for troubleshooting purposes using images that are stored on our server. Images are stored in sub-folders that pertain to that particular chip's serial number (basically a row/column designator), and the sub-folders are stored within a main folder with the wafer serial number. So, for example, 12345 would be the main folder (wafer s/n), then 1-1, 1-2, 2-1, 2-2, etc. would be the sub-folders (individual chip s/n's).

The top-level folder in which the main folder is stored on the server gets new folders added to it frequently as we get more wafers/chips in. For each wafer and set of chips we receive, we have to create a new spreadsheet with pictures which we are currently manually resizing and placing to fit within the cells. It is this process that I would like to "automate".

So, what I would like to do is create a template into which I can simply type the wafer's serial number, then have Excel look on the server for a folder containing that wafer's s/n, then look within that folder for any existing chip s/n's and grab the image file (.png), resize it, and insert it into the proper cell.

I hope I haven't confused anybody with this long-winded explanation/introduction to what I'm trying to achieve. Heck, I don't even know if it's possible without using VB. If anybody needs more clarification, please let me know. And I hope there's a way to do this using a formula instead of VB.

Thanks, in advance, for any help anyone is able to offer!

Chris

 

[jbarna]

This function fixes a lot of issues for me. I do have one issue though, my file size jumped 3 times the original size. It should have gotten smaller right? The way I understand it is the images aren't stored in the excel document if I'm using the true, false combo.

Is there something I'm missing in the document options?

Thank you in advance!

 

[Jhouhe]

First of All, thank you Damon. This is one of those excel things I've always wanted to do, but never figured out. You really made my day.

Greg,
Try using this formula to call Damon's function. You will need to create a folder at the same level as your excel file. Put any pictures you'd like to call in it. I used this formula and it worked perfectly.

=ShowPic(LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1)&"Folder Name\"&"B1")

(the name of the picture to be displayed is in Cell B1)

Hope this helps...

~ Dan

Damon,
Is there a way to delete the old picture each time the sheet recalculates, this way I don't have hundreds of pictures on the sheet?

Hi Sir Damon,

when i used the formula given on the above (=ShowPic(LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1)&"Folder Name\"&"B1"). i'm having a problem on the result. on the cell prompted #value!, do i need to be specified on the filename shows on the formula?

I have the same problem with gregg conrad. Please help me to finsh my work in excel i'm a newbie in VBA method. please send me a details procedure or step to make this kind of argument in the excel. (if A1=name, the photo will appear corresponding on that name indicated on A1.

thank you,
jhouhe

 

[Jhouhe]

Hi Sir Dan/Damon,
Kindly help me to figure out this matter. I have copied the VBA code

(Function ShowPicD(PicFile As String) As Boolean
'Same as ShowPic except deletes previous picture when picfile changes
Dim AC As Range
Static P As Shape
On Error GoTo Done
Set AC = Application.Caller
If PicExists(P) Then
P.Delete
Else
'look for a picture already over cell
For Each P In ActiveSheet.Shapes
If P.Type = msoLinkedPicture Then
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
P.Delete
Exit For
End If
End If
End If
Next P
End If
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
ShowPicD = True
Exit Function
Done:
ShowPicD = False
End Function
Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
On Error GoTo NoPic
If P Is Nothing Then GoTo NoPic
ShapeName = P.Name
PicExists = True
NoPic:
PicExists = False
End Function)

posted here on the VBA module. then I also copied the formula

(=ShowPic(LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1)&"Folder Name\"&"B1") ), then change to (=ShowPicD(LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1)&"C:\Documents and Settings\4275\桌面\Macro training\"&"B1"))

but i goteither #value! or FALSE result. I have a question also, do i need to put the formula on the B1 cell so that if the A1 will be call out?. Please teach the proper steps in figuring my problem.

Thank you very much

 

[MrDanielLG]

Hi guys! thank you so much. this blog has been a great help. i can't believe how are hard it is to simple make a picture dis-appear and re-appear based on a value. but thanks to you guys, i got it! success... so i thought... i tried to print it... and the pictures don't come out. is there anyway to modify this code or what do i need to do to get the pictures to print out?

thank you for your help so far. you are the man!

 

[Jhouhe]

Hi Guys,
Can you help me to figure out my query? here is the situation; when I enter value c2=1 the picture appear in c1. but when i enter 9 at c2 cell, the pic_09 doesn't come out, but when i enter 8, the pic_08 come out. I have made the picture to DEFINE NAME in every cells.

Name
pic_00

Refers to
=if(sheet1$c$2=1,pic_01,if(sheet1$c$2=2,pic_02.....,if(sheet1$c$2=8,pic_08,pic_159)))))) (i can't able to insert more conditions)

how can I put a formula on (refers to) so when I enter the pictures value appear in c1.

Your answer is much apprciated

 

[MrDanielLG]

Hi guys! thank you so much. this blog has been a great help. i can't believe how are hard it is to simple make a picture dis-appear and re-appear based on a value. but thanks to you guys, i got it! success... so i thought... i tried to print it... and the pictures don't come out. is there anyway to modify this code or what do i need to do to get the pictures to print out?

thank you for your help so far. you are the man!

i got it!... i had an internal excel error. the next day when i came in to work, it was working & printing fine. thank you guys so much for the formula!!!! it works great! now i am using it all over my excel sheet. thank you!

 

[colinrobinson]

I have followed the method from

http://www.mcgimpsey.com/excel/lookuppics.html

as it was posted way back at the start of this thread. It works great for me, but the problem is that I have pictures in my worksheet that I do not want to hide. Is there something I can add so that those pictures become visible again but the ones being used in the code do not.

Thanks

 

[kevinm2]

The function works perfectly.

The problem is when the function is being used in Workbook1, and Sheet1, while going to different sheets of that workbook, or when you open up a new workbook; the images get replicated on all sheets in the same cell area, same size and etc as Excel recalculates.

Which is a problem when 100s of images are being replicated, as you go.

So somehow it has be limited to when function is called!

 

[ASNAEEM]

Dear, i want to add picture in excel that will be selected against picture name in particuler cell. please remember that pictures folder will remain the same at all. if i will change the file name in cell, the picture must be changed.

Regards,
Abdul Sattar Naeem