Inserting Pictures as a Formula in Excel 2010

[KnightC]

Hi people can any one help I am looking to have a few pictures show as a formula but I am unsure on how to get it to work.

I need to do the following:

what will show in column B will change each day as a I am creating a ordering picking list and want to show a pic in column A of the item to help with picking the item. Is there an easy way of doing this.

I have been messing around with the following
=showpic("C:\Desktop\Items\46004978Thumb.jpg")
and thought I may be able to show this as by doing the following in the desired cells
=IF(ISBLANK(B1),"",INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0)))
on sheet1 I have all the model numbers next to =showpic("C:\Desktop\Items\46004978Thumb.jpg") and the index formula will grab the correct showpic string depending on stock code that is showing in various column B cells
but this isn't working
please can anyone help.

 

[Andrew Poulsom]

Welcome to MrExcel.

What is the function showpic?

 

[KnightC]

I found a previous thread on here going back to 2004 but thought it would work.

http://www.mrexcel.com/forum/excel-questions/100737-pictures-excel-formula.html

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

If you know an easier way for me to do this it would be a great help?

 

[Andrew Poulsom]

Maybe this video will help you:

Learn Excel 2010 "Dynamic Pictures": Podcast #1417 - YouTube

 

[cmoumi]

Hi, according to this I able to insert the picture surely in my excel sheet. But in that case the file path is fixed. If I would like to take the file path from a cell then how do I do? Any suggestion would be appreciable. Thanks,

 

[Andrew Poulsom]

Welcome to MrExcel.

Did you try eg?

=ShowPic(A1)

 

[cmoumi]

Welcome to MrExcel.

Did you try eg?

=ShowPic(A1)

Thanks for the reply. Actually I can't use cell number directly as I need to grab the cell from different sheet as well. I have more than one pictures to show whereas the pictures are sitting at one location. So now I used the filepath as argument in the ShowPic() function and it works finally and you also said the same thing. Now I have another queries pls. Could I use this function globally? I mean I can use now this function on that particular excel workbook. If I need to call it from other workbook what would I do pls. ?Thanks in advance.

 

[Andrew Poulsom]

You could put the function in an Add-In.

 

[cmoumi]

You could put the function in an Add-In.

Ok. Thanks. Sorry, I don't know how to do it. Could you explain in detail that how could I use Add-in pls.? Many thanks in advance.

 

[Andrew Poulsom]

Here's tutorial:

Create Addins