Page 1 of 7 123 ... LastLast
Results 1 to 10 of 61

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. #1
    New Member
    Join Date
    Sep 2004
    Posts
    16

    Default 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
    Gregg Conrad

  2. #2
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,308

    Default Re: Pictures in Excel Formula

    Last edited by smozgur; Dec 31st, 2008 at 06:27 AM. Reason: thread URL fix

  3. #3
    New Member
    Join Date
    Sep 2004
    Posts
    16

    Default 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. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,219

    Default 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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm
    (My other life: http://damonostrander.com )

  5. #5
    New Member
    Join Date
    Sep 2004
    Posts
    16

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

    Gregg

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    Post the code you are using..... Check that you have the correct path to the images.
    Kind Regards,
    Ivan F Moala From the City of Sails

  7. #7
    Board Regular PA HS Teacher's Avatar
    Join Date
    Jul 2004
    Location
    Rochester, NY
    Posts
    2,838

    Default 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. #8
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,219

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

  9. #9
    Board Regular PA HS Teacher's Avatar
    Join Date
    Jul 2004
    Location
    Rochester, NY
    Posts
    2,838

    Default 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. #10
    New Member
    Join Date
    Sep 2004
    Posts
    16

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

    Gregg

Page 1 of 7 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com