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

 

[yorkshirelad]

Its actually worse than that, if I have multiple spreadsheets open, whenever I make an entry in any workbook the pictures arrive on that page too.

I would appreciate your thoughts.

Many thanks

Mike

 

[PA HS Teacher]

Mike,
- I've not seen this projectile spreadsheet. I have one I wrote for my students, but it is not nearly as ornate. I also like to use java based "physlets" that are becoming more readily available on the web. Thank you for showing me.

You are getting pictures because, the code places the pictures on the active sheet. Assuming the pictures always go on the same worksheet:

- Wherever you see Activesheet in the code, replace it with:
Worksheets("Sheet 1") [replace "sheet 1" with the actual name of the worksheet with the pictures] I believe you will need to do this in two places.

As far as a border, the code could be modified so that a blank picture is placed on the sheet underneath the desired picture. You could say, use paint to create a pretty box. Save it as a jpg. Then right before the line:
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, False, AC.Left , AC.Top, Width, Height)
Add the line
Set P = ActiveSheet.Shapes.AddPicture("C:\\...Pretty Box.jpg, True, False, AC.Left -20, AC.Top -20, Width +40, Height + 40)

 

[yorkshirelad]

Thanks Dan - that's sorted now and I really appreciate your suggestion for the frame too, that's neat.

Thanks again to both yourself and Damon for all your help.

Kindest Regards

Mike

 

[dadof3]

ray:

Wow! I don't know if this forum is over my head, but this is exactly what I need to do with a project I'm working on.

Does anyone have a example of this they could upload for excel "wanna-be's" like myself to try and learn off of?

I'm not a much of a "coder", but I'm a quick learner.

 

[Damon Ostrander]

Hi dadof3,

If you would like to either email me or PM me with your email address, I will email you back a sample workbook.

Damon

PS. I'm also a dadof3 -- 2 g, 1 b. Good luck.

 

[leibale]

how about this code:

Function ShowPicD(PicFile As String) As Boolean
Dim AC As Range
Dim P As Shape
On Error GoTo Done
Set AC = Application.Caller
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
On Error Resume Next
ActiveSheet.Shapes("Temp").Delete
P.Name = "Temp"
ShowPicD = True
Exit Function
Done:
ShowPicD = False
End Function


it delete the previous Temp Picture

can someone tell me what exactly is the first True argument
is the picture linked to the file?

 

[Damon Ostrander]

Hi leibale,

Yes, the first True is the AddPicture method's LinkToFile argument. When TRUE the picture in Excel is linked to the file, otherwise a copy of the picture is stored in Excel. I recommend TRUE if you have many large pictures and your Excel file is not used by other users who do not have access to the picture files at the same path.

Damon

 

[leibale]

but, the picture is not linked

Hi leibale,

Yes, the first True is the AddPicture method's LinkToFile argument. When TRUE the picture in Excel is linked to the file, otherwise a copy of the picture is stored in Excel. I recommend TRUE if you have many large pictures and your Excel file is not used by other users who do not have access to the picture files at the same path.

Damon

when I'm using the True, True - the picture is not linked.
I can delete the picture fron the folder, and the picture is stil there until I calculte again the worksheet
may be, the excel "decide" not to link it because it is small picture?
in powerpoint there is something like this. some times the added pictures are part of presentaion and sometime there is a link to the pictures

 

[Jorne]

What a great topic! Special thanks to Damon and DA HS Teacher

One question : I've pictures with different heights, weights (and proportions). (How) Is it possible to adjust the picture so that the height will be 100 pixels and the weight is adjusted accordingly with the keeping the proportions right?

 

[Jorne]

Fixed it by adding:

P.ScaleHeight 1, True
P.ScaleWidth 1, True
P.Height = 100

Underneath row Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 100, 100)