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]

That's sorted it, thanks very much for your help. The final challenge related to the formatting of the cells. I had called the image 0001.jpg but needed to change the format to text for it to call the correct image file.

I have a couple of final questions if you can assist.

1. Now I've got it working I need to move it to the proper worksheet. If I want the image to appear in cell L8 and have the image name e.g. 0002 in cell K8 how should the formula work?

I have obviously made an error but if I enter the formula

=LEFT(CELL("FileName",A7),FIND("[",CELL("FileName",A7))-1)&"stockpics\"&"K7" into cell L7 the image appears but it is located in cell B19?

2. All my images are different sizes. Although I have a macro that will change the image size to make it 125 pixels wide and keep the proportions correct once it is one the page - is there a way to do that automatically?

Thanks again for all your brilliant guidance.

Kind Regards

Mike

 

[PA HS Teacher]

Picture Size:
In the code for ShowPicD, Find the line

Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)

Change 200 to whatever pixel size you desire, or change the 200 to Range("Cell with desired picture size in pixels")

Picture Location:
The picture should always be placed on the spreadsheet so that that upper left corner of the picture is lined up wi the upper left corner of the cell calling the ShowPicD function.

When you enter the ShowPicD() function in some cell, say... C25, which cell does the upper left edge of the picture wind up?

And, if you then enter the Show PicD() function in another cell, say E25, which cell does the upper left edge of the picture wind up? Is it off by the same amount? If so, the offset function could be used so that the picture is always located in the same place, relative to the cell that does the calling.

Relative or Absolute references:
When/If you copy or paste this function on your spreadsheet, consider whether you want to use relative or absolute references. e.g. K8 or $K$8.

As far as the worksheet: This function should work in any worksheet (assuming you've added that ShowPicD function to that workbook.)


~ Dan

 

[yorkshirelad]

Thanks Dan

I will work on those options. One question though - is this formula correct to put an image in L7 (assuming I get the offset correct) and the details of the image is placed in cell K7? What is the formula looking for in cell A7 in the CELL and FIND sections - I haven't got anything entered in those cells.

=LEFT(CELL("FileName",A7),FIND("[",CELL("FileName",A7))-1)&"stockpics\"&"K7"

Many thanks

Mike

 

[PA HS Teacher]

Mike,
The A7, could actually be any cell in the workbook, it doesn't matter. The funtion returns the file name and location, it requires a cell reference, any cell reference, for an argument.

The picture should show up in the cell calling the funtion. E.g. If you put,
=ShowPicD(....) in cell L7, the picture will show up with the upper left edge lined up with the upper left edge of cell L7.

Putting =ShowPicD(...) in cell AA467 would put the picture in cell AA467 etc.

I don't think you will need to mess with the offset function

 

[yorkshirelad]

Hi Dan

Thats sorted the pictures are going into the cells correctly but I have one final challenge.

I could be wrong but as the resize option for the pictures will only resize them to a definate size in other words not in proportion, I am having to include the pictures and then resize them.

However because the sheet consistently recalculates after every entry, it keeps on bringing all the pictures back in at their original size! e.g when I enter the formula for the tenth picture - I have ten new pictures all at the wrong size!

Any ideas?

Thanks again

Mike

 

[Damon Ostrander]

Hi again Mike,

Several possibilities come to mind. If you you want to always size a certain fraction of the original size, a zoom factor could be incorporated in the function's argument list. This could be an optional argument so that if not used the zoom factor would be 1. So your call to ShowPicD would look like

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

to scale the picture 50% of original size.

If the desire is to shrink the picture proportionally so that the width or height (or both) does not exceed a certain value, or perhaps fits within a certain number of rows or columns on the worksheet, this can also be done by adding an additional argument or arguments to the function.

Damon

 

[PA HS Teacher]

Mike,
There are many ways to handle this. I'm sure Damon could write much more creative ways to handle this (such as the fraction of original size method he has mentioned), but a quick solution could be:

Add Width and Height arguments to the ShowPicD function. To do this change the following two lines:

Function ShowPicD(PicFile As String,Width as Integer,Height as Integer ) As Boolean

Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, Width, Height )

Now, if you were to enter: =ShowPicD(..., 100, 100)
The picture should be 100 x 100 pixels, no matter what the original size of the file. In another cell you might choose a different size. Perhaps keep the number of pixels in columns L and M.
e.g. =ShowPicD(...&K7, L7, M7)

A drawback to this method is that you must specify a size for each picture.

- I just thought I'd throw in two more cents

 

[yorkshirelad]

Fantastic guys - that's done it as all the pictures are taken with the same digital camera I can reduce them to a standard pixel size.

I don't suppose there is a way of putting a simple border round the picture is there? I would put a border round the cell but the cells containing the pictures are merged, and if there isn't a picture I don't want to bring notice to it.

Thanks again for your help.

Mike

 

[CT Witter]

Slightly off topic...

Since you are doing physics and trajectories, have you downloaded the sample from http://www.mrexcel.com/pc11.shtml

.. Entry 2:
Animated Cannon Chart


I've used this many times and students get a real kick out of it.

 

[yorkshirelad]

I'm not sure about the last posting - I'm sure that's been posted on the wrong topic.

I do have a challenge though. The workbook has multiple sheets and each time I make an entry on another worksheet all the pictures from the front page are duplicated on the current sheet.

I didn't expect that - have I done something wrong. Can I make the module just relate a specific sheet?

Many thanks

Mike