Pictures in Excel Formula

See if one of these help

Displaying Images as a result of an if statement
Pictures as Data in Cells
Return a Picture

HTH
texasalynn

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

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.

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.\
Please help if you can

Gregg

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

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 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.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
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

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

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.
Please help

Gregg