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

 

[PA HS Teacher]

Damon,
Your updated function works perfectly until I:

- Change the argument of the ShowPicD() UDF.

For example ShowPicD("...Per 1 File\Student 1.bmp") will work.
But if I change it to
ShowPicD("...Per 1 File\Student 2.bmp") it stops working.

After I've done that, the function only returns FALSE. I'm assuming that means it gets an error each time the function is called.

I circumvented this a little bit by using the offset function so that I can change the pic by changing the argument in ShowPic Refers to, and this works until the argument is a file that does not exist.

If I close the file and open again, the process repeats. It works until I change the argument to the ShowPicD function.


Any ideas...

Thank you for your help. (please feel free to take your time, this is an ongoing project for me.)

~ Dan

 

[Damon Ostrander]

Hi again Dan,

Yes, my mistake. I overlooked the fact that when the picture is deleted manually or deleted by the code and then the code fails to add a new one, the variable P becomes a "dead pointer", neither Nothing nor containing a valid pointer to a picture. This code should take care of that problem. Note the addition of the PicExists helper function.

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


Damon

 

[PA HS Teacher]

Damon,
Your function works perfectly. My random seating chart maker, now has all the students' faces on their desks! When I mix up the students, now their faces move as well as their names. (With over 100 students, this is a huge help!) I'd been pondering how to make this happen for number of months now.
Your function also allowed me to include the student pictures without increasing the file size of my spreadsheet. The student pictures are saved as individual pictures in folders by period. (Per 1, Per 2 etc.) I modified your code slightly (very slightly, as you did the hard part) so that I can resize the pictures and shift them, based on the values I placed in a couple of cells. What can I say, you've made my day. Thank you for putting in the time.

~ Dan

 

[yorkshirelad]

Thanks for all this guidance guys - I've just visited the board today with a hope there would be a solution to a challenge I currently have and the above posts have nearly got me there.

I still have an error however - and wondered if someone could shed some light on the problem.

When I enter the formula
=ShowPic("C:\Documents and Settings\Mike\My Documents\ANGR\stockpics\0002.jpg") I don't get any problems, the picture comes up great.

However as I need the picture to be dynamically linked to the contents of another cell I have entered the formula below (as posted by Dan PA HS Teacher) but get #VALUE.

This is what I have entered, into cell C2 of the Promotions workbook.

=ShowPic(LEFT(CELL("Promotions",A2),FIND("[",CELL("Promotions",A2))-1)&"stockpics\"&"B2")

Unfortunately as I haven't created the formula I can't follow it - which I generally can. Promotions is the name of the excel workbook, and stockpics is the name of the folder where the pictures are. This folder is contained in the ANGR folder where the worksheet Promotions is also. I have the details of the pics 0002.jpg entered in both A2 and B2.

If anyone could shed any light on this it would be much appreciated. I have added both ShowPicD and ShowPic functions.

Many thanks

Yorkshirelad

 

[PA HS Teacher]

Yorkshirelad,

Try this: =ShowPic(LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)&"stockpics\"&"B2")

The Cell Function will give you information about the file. It is a useful function, you can look it up in the help menu. "FileName" is an actual argument, don't change it to the name of the file. To see what I mean enter

= Cell("FileName",A1) in any cell. (A1 can be any cell in the workbook)
It will return the file name and location of the file.

The Left and Find Commands are used to extract just the location of the file.

If you enter
=LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)

It should return:
C:\Documents and Settings\Mike\My Documents\ANGR\

If the ShowPic or ShowPicD function still has an error, try entering

=LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)&"stockpics\"&"B2" into a cell

See how close it comes to:
C:\Documents and Settings\Mike\My Documents\ANGR\stockpics\0002.jpg

Let me know you make out

~ Dan

 

[yorkshirelad]

Hi Dan

Many thanks for your help
if I enter
=LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)

it does return...
C:\Documents and Settings\Mike\My Documents\ANGR\

If I enter
=LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)&"stockpics\"&"B2" into a cell

it does return
C:\Documents and Settings\Mike\My Documents\ANGR\stockpics\0002.jpg

But for some reason when I enter into cell C2 the new formula

=ShowPic(LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)&"stockpics\"&"B2")

result is FALSE and no picture.

Have you any ideas?

Many thanks

Mike

 

[PA HS Teacher]

Are you using the ShowPic or ShowPicD functions?

If using ShowPicD, I'd like to look at the code to see which version you were using. I had a similar problem when I first used the ShowPicD function, but then Damon fixed something. (had to do with a dead pointer)

If using ShowPic, try ShowPicD

The advantage to ShowPicD is that it deletes the old picture before putting down the new one. Otherwise, a picture is put down every time the sheet calculates. Leaving you with a huge stack of pictures.

 

[yorkshirelad]

Hi Dan

Thanks for your reply

I am using the ShowPicD function and have adjusted the formula to reflect that. I have created a new module and entered the function as below. I am still just getting FALSE in the cell.

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

Any thoughts would be greatly received.

Mike

 

[Damon Ostrander]

Hi Mike,

One thing I would suggest before looking for other possible causes of the problem would be to copy the string

C:\Documents and Settings\Mike\My Documents\ANGR\stockpics\0002.jpg

and paste it into the file browser's Address box. The file browser should immediately open the 0002.jpg file in your default application for displaying .jpg files. If not, you should get the familiar "The page cannot be displayed" screen, which would mean that there is something wrong with the path or filename in the string (i.e., no such file exists at this path).

If this does not shed any light on the problem I invite you to email your workbook to me and I will debug it.

Damon

 

[PA HS Teacher]

Mike,
I opened a blank workbook and posted the ShowPicD() function exactly as you posted it. I even created a stocpic folder and a 0002.jpg file. It worked fine for me:

=ShowPicD(LEFT(CELL("FileName",A2),FIND("[",CELL("FileName",A2))-1)&"stocpics\"&B2&".jpg")

Where contains: 0002


Perhaps you could add the & ".jpg" after B2

Otherwise, I don't think its Damon's Function. You should check the file location. Make sure that is where the file really exists.