+ Reply to Thread
Results 1 to 17 of 17

Multiple picture lookup with McGimpsey's method

  1. #1
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Multiple picture lookup with McGimpsey's method

    Hello,

    I stumbbeld upon McGimpsey's method to lookup a pic based on a cell's value. I got it to work for myself fairly easy. Yet I have not managed to
    get it to work for multiple cells.

    What I got to work:

    If cell A2 contains a certain value a related pic appears.

    What I didn't got to work:

    If cell A2 contains a certain value a related pic appears and if cell A3 contains a certain value a related pic appears. But not necessarily the same
    value an pic, but that could be possible, since the value in A2, A3 etc. is selected by a drop-down list through Data-validation.

    The VBA-code I used:

    Please Login or Register  to view this content.
    I basically need an adjusted code which lets me choose a value en show a picture in an infinite number of cells, than only cell D1. I want the same function for other cells.

    I already tried:

    With Range ("D1";"D9"), but then both these cells just need to have the same value, and a picture is shown.

    The Excel file I used is embedded in this post.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by arlu1201; 06-14-2012 at 02:39 AM. Reason: Code tags

  2. #2
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Multiple picture lookup with McGimpsey's method

    Try this:

    Picture test VBA.xlsm

    I changed it to use the worksheet_change event, and updated the list on Blad2 to include your actual picture names. The second function in the Blad1 module lists all the pictures in your workbook.

  3. #3
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Multiple picture lookup with McGimpsey's method

    Thanks wallyeye, but it doesn't seem to work for me. The thing I wanted, as stated in my opening post, I got to work using a non-VBA method. But I managed to do this in the past, yet my Excel-file got really slow.
    As soon as I removed the formulas which made the pictures show based on value, the file was as fast as can be. That's why I want a VBA method.

    So if you can take a look in the Excel file from this post (specifically worksheet Main and PictureLookup), maybe you can show me a method of doing this via VBA and how to do this for an infinite number of cells.

    Many thanks.
    Attached Files Attached Files

  4. #4
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Multiple picture lookup with McGimpsey's method

    OK, so I move the pictures to Blad2, made them visible (you can hide the sheet). Changed the worksheet change macro to copy the picture from blad2 to blad1, into the corresponding row of the changed cell. The macro checks if the changed cell is in column 1 and has data validation associated with it. It then deletes any pictures with the given name (Pic + cell address), looks for the corresponding picture on Blad2, copies it, renames it.

    Picture test VBA.xlsm

  5. #5
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by wallyeye View Post
    OK, so I move the pictures to Blad2, made them visible (you can hide the sheet). Changed the worksheet change macro to copy the picture from blad2 to blad1, into the corresponding row of the changed cell. The macro checks if the changed cell is in column 1 and has data validation associated with it. It then deletes any pictures with the given name (Pic + cell address), looks for the corresponding picture on Blad2, copies it, renames it.

    Attachment 159058

    Thank you very much.

    This was exactly what I was looking for! I'm now trying tot adapt your code to my own Excel-file.

    Many thanks.


    Edit:

    Based on the code, I have a couple of questions:

    - The pictures being shown are based on 1 database, is it possible to make three or four databases? This would be used as there are different data validation lists.
    For example.

    In Cell A1 I have a data validation list named productlist1
    In Cell H1 I have a data validation list named companylist1

    If I choose a product in Cell A1, for example hamburger. It shows a hamburger based on product database (pictures) and than for Cell H1 I choose a company, for example MacDonalds, based on the company database (pictures).

    So basically different pictures shown based on different data validation list, or just any given value and in any given cell.

    I've explained it also in the included Excel-file in this post.

    It is much appreciated if you could take another look!

    Kind regards.

    Kopie van Picture test VBA 2.xlsm
    Last edited by ruttiii; 06-04-2012 at 05:01 AM.

  6. #6
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Multiple picture lookup with McGimpsey's method

    It would be similar logic, you could still have all your pictures on the one sheet, or separate sheets. In Blad4, you have your validation list adjacent to your picture name list, your code would be:

    Please Login or Register  to view this content.
    This assumes that you have your pictures on Blad4, named the same as the lists in column B and J. Note that right now, your lists are the same, they need to be the same as named pictures embedded on the sheet.

  7. #7
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by wallyeye View Post
    It would be similar logic, you could still have all your pictures on the one sheet, or separate sheets. In Blad4, you have your validation list adjacent to your picture name list, your code would be:


    This assumes that you have your pictures on Blad4, named the same as the lists in column B and J. Note that right now, your lists are the same, they need to be the same as named pictures embedded on the sheet.
    Thanks Wallyeye,

    I've been trying to get your code to work, but I haven't been able to succeed.


    Is it also possible to create a code where I can write for every cell I choose to, a certain image displays (in a chosen cell)?

    For example:

    Cell A2
    If contains Hamburger -> show picture of Hamburger
    If contains Chicken -> show picture of Chicken

    Cell A11
    If contains Hamburger -> show picture of Hamburger
    If contains Chicken -> show picture of Chicken

    Cell 20
    If contains Hamburger -> show picture of Hamburger
    If contains Chicken -> show picture of Chicken

    etc. etc.

    But also a completely different cell value

    Cell C2
    If contains Burger King -> show picture of Burger King
    If contains KFC -> show picture of KFC

    In this example the could be loaded from the worksheet or C:\pics\food\....

    I know this would probably be a bit inefficient as I will have to determin the outcome for every cell, but at least I will understand the code.

    Thanks!

  8. #8
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Multiple picture lookup with McGimpsey's method

    Load Blad4 up with your visible pictures, naming them according to the two tables you have set up. You will need to change the names in either column B or column J, as you cannot have the same picture name twice on a worksheet. When you have this, set up Blad 3 like you want it, either scattering the the datavalidation cells around, putting them in columns or single cells. Re-post the sheet, I'll tweak my first code or second code to match your changes.

  9. #9
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by wallyeye View Post
    Load Blad4 up with your visible pictures, naming them according to the two tables you have set up. You will need to change the names in either column B or column J, as you cannot have the same picture name twice on a worksheet. When you have this, set up Blad 3 like you want it, either scattering the the datavalidation cells around, putting them in columns or single cells. Re-post the sheet, I'll tweak my first code or second code to match your changes.
    I think I got lucky, because I managed to get it to work. Yet I still have a small issue. I modified your code to the following:

    Please Login or Register  to view this content.
    How it works now:

    - I added a new validation list in a different sheet.
    - Only if I choose the name of the picture from that drop-down list or write it manually it works.

    If I enter the name of the pic based on VLOOKUP, sadly it doesn't work.

    Can you see anything in my code, which prevents the code from working if a cell in Collumn A contains the name of a picture via VLOOKUP?

    Many thanks!

  10. #10
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Multiple picture lookup with McGimpsey's method

    The worksheet_change event does not trigger for the vlookup formula, only for the manually changed values. To trigger the event, you would have to point the Target to the value you are changing.

  11. #11
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by wallyeye View Post
    The worksheet_change event does not trigger for the vlookup formula, only for the manually changed values. To trigger the event, you would have to point the Target to the value you are changing.
    Thanks! I understand what you're saying. Indeed to make a long story short, I have to change it manually.

    But if I understand your answer correctly I have to change the code so it interacts with every single target?

    Or is it possible to create a code that says: if any cell in a defined collum contains a pre-selected value, it will show a pre-selected picture (based on the value) 3 cells to the right?

    Many thanks!

  12. #12
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: Multiple picture lookup with McGimpsey's method

    My 5/31 10:29 post has code in it to check if the changed cell is in column A and has data validation tied to it.

    Please Login or Register  to view this content.
    Another thing you could do is use a case statement:

    Please Login or Register  to view this content.
    or if they are consistently spaced:

    Please Login or Register  to view this content.
    There are a number of ways to validate you have a good cell based on Target properties.

  13. #13
    Registered User
    Join Date
    03-15-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by wallyeye View Post
    My 5/31 10:29 post has code in it to check if the changed cell is in column A and has data validation tied to it.

    Please Login or Register  to view this content.
    Another thing you could do is use a case statement:

    Please Login or Register  to view this content.
    or if they are consistently spaced:

    Please Login or Register  to view this content.
    There are a number of ways to validate you have a good cell based on Target properties.
    Thank you very much! I'm going to have a go with it.

    Kind regards.

  14. #14
    Registered User
    Join Date
    10-31-2018
    Location
    NCR, Philippines
    MS-Off Ver
    2007 & 2016
    Posts
    1

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by wallyeye View Post
    OK, so I move the pictures to Blad2, made them visible (you can hide the sheet). Changed the worksheet change macro to copy the picture from blad2 to blad1, into the corresponding row of the changed cell. The macro checks if the changed cell is in column 1 and has data validation associated with it. It then deletes any pictures with the given name (Pic + cell address), looks for the corresponding picture on Blad2, copies it, renames it.

    Attachment 159058
    Hi Wallyeye,

    Appreciate this as this is what i'm also looking for but how do you apply this code to select rows? (e.g; rows 3, 8, 11, etc.)

    Thanks in advance.

  15. #15
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Multiple picture lookup with McGimpsey's method

    Good morning ar_dizon

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too. Also. bear in mind that walleye hasn't posted on this forum now for well over four years.
    Please familiarise yourself with the rules before posting. You can find them here.

  16. #16
    Registered User
    Join Date
    03-04-2020
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    1

    Re: Multiple picture lookup with McGimpsey's method

    Hi,
    i was having a similar problem and your solution seems the best. now i am facing two problems

    1- i need the picture to be displayed in one any cell. means the picture should not go out of the cell.
    2- i need to move the column A to any other column, but when i do so, the dropdown doesnnt work.

    can u help me with these two issues plz?

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiple picture lookup with McGimpsey's method

    Quote Originally Posted by AMCCON View Post
    Hi,
    i was having a similar problem and your solution seems the best. now i am facing two problems

    1- i need the picture to be displayed in one any cell. means the picture should not go out of the cell.
    2- i need to move the column A to any other column, but when i do so, the dropdown doesnnt work.

    can u help me with these two issues plz?
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1