# Pictures in Excel Formula

This is a discussion on Pictures in Excel Formula within the Excel Questions forums, part of the Question Forums category; Hello I am trying something new. I would like to find out how to put a picture into a formula ...

1. ## Pictures in Excel Formula

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

3. ## Re: Pictures in Excel Formula

Thanks
I thought it wold be easier. Thanks for the direction to step in. If you can think of anything else please let me know

Gregg

4. ## Re: Pictures in Excel Formula

Hi Gregg,

There is an easier way. Here is a user-defined function (UDF) that will do what I believe you describe:

Function ShowPic(PicFile As String) As Boolean
Dim AC As Range
On Error GoTo Done
Set AC = Application.Caller
ActiveSheet.Shapes.AddPicture PicFile, True, True, AC.Left, AC.Top, 200, 200
ShowPic = True
Exit Function
Done:
ShowPic = False
End Function

To install this, go to the Visual Basic Editor (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane. Then go back to Excel, and you can call this function like this:

=showpic("c:\my documents\my pictures\Larson07b.jpg")

This function will display the picture over the cell with the top left corner of the picture on the top left corner of the cell. I did not include code to size the picture in width and height to fit into the cell, but if you want this I believe it is easy to add. As it is it sets the width and height to 200 points. In addition it does not delete the previous picture when you change the picture path argument to load a different picture.

The function will yield a value of TRUE if it finds the picture at the path you give it, and FALSE if it doesn't.

5. ## Re: Pictures in Excel Formula

I am still havin trouble matcing the picures to theworksheet. in the examples in have received, i can not find the pictures anywhere on the worksheet.
I think the prolem is in the VBS. I am not refering to the pictures in the code.\

Gregg

6. Post the code you are using..... Check that you have the correct path to the images.

7. ## Re: Pictures in Excel Formula

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?

8. ## Re: Pictures in Excel Formula

Hi Dan,

Yes, I guess it was just a matter of time before someone asked for that. Here's modified code that deletes the old picture each time it recalculates. Normally it will remember the old picture (the P variable), but when the file is first opened it has no memory of the previous picture so I added logic for this case to delete the first picture it finds with its upper-left corner in the cell. I named this function ShowPicD:

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 P Is Nothing Then
'look for a picture already over cell
For Each P In ActiveSheet.Shapes
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
Else
P.Delete
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

So, are you a HS teacher in Pennsylvania, Palo Alto, or ? (Either way, you are to be commended).

Damon

9. ## Re: Pictures in Excel Formula

Thanks Damon!
I can't wait to try it today after school. I am a HS physics teacher in Pennsylvania. I've really become addicted to Excel over the last couple of years. (A few months ago I discovered this board. My eyes have been opened, and I'm afraid there's no turning back now.) I really like using excel so that students can "do" problems that are mathematically over their head. A black box approach, where they change a number, say the velocity of a projectile, and the sheet "magically" gives the path of the object without necessitating the calculations. I find this helps students see the pattern before I teach them how to do the problem for themselves.

I also make spreadsheets for all sorts of things, cross country scoring, Random Vocabulary Quizzes and a Random Seating Chart maker. It's the seating chart maker that could really use your function. With your updated function I hope to place student pictures on their desks. This will be invaluable at the beginning of the school year. Thank you for your help!

~ Dan

10. ## Re: Pictures in Excel Formula

My main problem is I do not know where to put the pictures.
Do I hide in the main worksheet? or do I put them next to the pic table?
When I move the marco to my live worksheet, I do not have any pictures and there is an error in the marco.

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

This example from the above website is perfect but I am having the problem stated above and a problem with the macro not finding the pictures. Also, I need the pictures in the worksheet because it will be located on a website for my users.

Gregg

Page 1 of 7 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•