Can you VLOOKUP a picture?

If I had a series of pictures in a worksheet, how could I give them a value so I could use the vlookup function?

I haven't found a work-around for the picture issue yet. From experience, the McG coding works well for up to 50ish pictures (I think it's size dependant too). You got the something about Picture.Visible.Me or Picture Class or something or other, right? Same problem here.

Does this mean there is no solution?

Is there something that could be done with the "image" control in the Control Toolbox? Could the images be separate forms that would be called up a value is given in a cell?

Just dimension as a Shape. The line

Please Login or Register  to view this content.

will not work, so replace it with an For ... Each to make any shape that is a picture not visible.

Please Login or Register  to view this content.

Leah, I am at least as ignorant of your workbook as you say you are of VBA. So, bear with me. I need to ask some questions that might seem pretty silly in order to understand, and therefore possibly be of help to you.

The actual Pictures are in Options, Column G

Not sure what this means. Pictures cannot actually be in cells. So, this could mean:
1. the Name of the picture is in column G
2. the name of a file containing the picture is in column G
3. the pictures are arranged on the sheet so that they appear to be in column G

I guess the bottom line is this ... are the 10 pictures themselves already in the workbook? Or only references to them?

Proposals!A92 is a drop down list with a choice of options

I assume this to mean that Proposals!A92 has cell validation; validation type is list. Let me know if I am reading this incorrectly.

You want when the value of Proposals!A92 changes, a picture to appear and any other picture that is visible to disappear. Correct?

Where do you want the picture to appear?

Sorry, I will clarify here.

Options is another sheet, and I have the pictures arranged so that they appear over certain cells in column G, as was advised on one of the help sites I read. Right now, the pictures as they appear in Excel are just labeled Picture 1, Picture 2, etc, in the top left corner of the screen. I have not assigned them names of any sort within the workbook. So, to specifically answer your question, the pictures are physically in the workbook.

The proposals sheet is the only sheet the end user will ever see. Yes, I have a validated drop down list at A92 for the user to choose an option. When he chooses an option, I want the picture that corresponds to that option to appear in the area of column J, next to the chosen option. I have about 14 spaces just like A92, so that the user can choose several different options, and I would like the corresponding picture to appear for each one.

I'm just attempting to follow what I read about doing this, and obviously missing the boat somewhere. I'm happy to answer any other questions you have. Thanks for your help.

I understand better now. I was thinking about the earlier postings in this thread as the starting point of my thinking. I am now re-oriented to thinking about mcgimpsey's example as the starting point.

The biggest difference is that in mcgimpsey's example, the pictures are on (what for you would be) the Proposals sheet; whereas in your case the pictures are on the Options sheet. Which is OK. Indeed, I agree that for what you want, they should be ... a person might make the same selection more than once (given 14 cells to make selections and only 10 pictures ... it is highly likely to happen).

OK ... I can work with this. And, unless someone esle already is working on it ... I'll post something here for you to use. Even better, I'll try to explain it!

The attached workbook does what I think you've asked for.

There are 2 sheets ... Proposals and Options. The Options sheet has a list in column A, names of pictures in column B, and pictures with the names shown in column B are hovering over column G.

The range containing the list in column A is Named "List". Not very original, I know.

On the Proposals sheet are 4 cells (highlighted), starting in row 92, with cell validation tied to the List in column A on Options.

When you select from the list, it triggers a macro to run. I will explain how it works. If you want to follow along, right-click on the Proposals tab and select "View Code" from the context menu. Then, scroll to the top of the code window.

The first VBA routine is a subroutine named "Worksheet_Change". Excel causes this routine to "fire" any time any change is made on the worksheet. This is called an "Event" procedure because it responds to an "event". In this case the "event" is any change made on the worksheet. When Excel "fires off" this procedure, it tells VBA which cell was changed. This is called the "Target" range.

We want to ignore most changes. So, the sooner we can decide that the change is something to be ignored, the better. The way I have set up the Proposals sheet, all of the selection cells (the only thing we care about for now) are in column A (column 1). If this is NOT the way you will have your Proposals sheet set up, then delete the line that reads:
If Target.Column > 1 Then GoTo leave

The next thing in this subroutine is a list of the addresses of cells that we are "watching". This example is watching 4 cells. You can see where I put the addresses for these 4 cells. If you have 14 cells you want to watch, then list them accordingly.

Next, we compare the address of "Target" with the list. If any of them match, we call on another subroutine that will pull in the correct (we hope) picture. That other subroutine is named "showPic". The code for it is directly below the "Worksheet_Change" procedure.

We pass to "showPic" the cell that the user changed. For no particular reason, I am calling this "vCell" (validated cell).

The first thing the "showPic" subroutine does is tell Excel to turn off screen updating. This allows us to make changes without Excel needing to re-paint the screen until we finish. That makes the routine run faster and appear a little bit more "magical" to the user.

On the Options page, pictures were named "Picture 1", "Picture 2", et cetera. On the Proposals sheet, any picture can go in any of the validation cell rows. So, we are going to name them for the row number we stick them in.

We do not want two pictures there at the same time; so, the first thing we do is, if we find a picture named for the Row associated with the validation cell (vCell), we delete it.

You will notice that I put in "On Error Resume Next". At some point in the future you will probably want a routine that "cleans up" the Proposals sheet from time-to-time. Which means there might be no picture associated with one or more validation cells. So, trying to delete such a picture would cause an error. That's why I tell VBA to ignore an error at this point.

Next, we define the sheet with the List and the pictures on it; namely, the "Options" sheet. If you ever change the name of the Options sheet, you will need to change this line of code. FYI ... you can change the name of the Proposals sheets any time you want and not have to change the code because we do not use its name anywhere. The word "Me" refers to the Proposals sheet ... but, be careful to have this clear in your mind ... this is only because the code itself is "written on" the "code window" of the Proposals sheet.

The List that appears in the validation cells comes from Column A (column 1) of the Options sheet. So, we want to search this column to find a match with the selection the user made. I defined a variable for this ... "searchRng" ... and set it equal to the Options worksheet's column 1. There should always be a match, of course ... that's what cell validation is all about. But, ya know, users can screw up even our best attempts. So, to be on the safe side, I tell VBA to ignore any errors before attempting to find the match. Then, I use the VBA equivalent of the worksheet function named MATCH. If you're not sure how this works, back in the normal Excel workbook, use Insert >> Function, find the MATCH function from the list, and ask for help. The "searchFor" and "searchRng" are kind of obvious. The third parameter is zero ... which means we are looking for an exact match.

Since I am searching the entire column, the match function will return the row number where the match was found (if one was). If there was no match, we clear the error created and get out of Dodge. If a match was found, we read the name of the picture we want from column B of the Options worksheet.

One of the limitations that has been noted for the mcgimpsey example is that it seems to work fine for a small number of pictures, but fails for a large number of pictures. Not sure why. But, I avoid that issue by referring to "Shape" objects instead of "Picture" objects. Shape is a more general term; Picture is more specific. It would be like referring to Vehicles instead of Trucks.

Instead of changing the Visible property of the picture as mcgimpsey does, we are copying the pictures from the Options sheet to the Proposals sheet. Then aligning it with the validation cell row and Column J (column 10).

The last thing we do is turn back on screen updating so the user can see our wonderful work product.

Hope I've explained this well enough. If not, write back.

Thank you SOOOO much!!! I will not be working on this again until Monday, so I'll let you know how it's going, but I appreciate the time you took to explain this to me so much! I'm going to print everything right now. Thank you thank you and thank you.

To add to this, I have placed the code right above the last code you gave me. When I make a change, it is giving me an error:

Compile error:
Amiguous name detected: Worksheet_change

I would assume that now I have to define each change somehow? How would I do that?

Oh, and both of these things will be occuring on the same page.

Apologies for the bump, but I'm a little desparate.

Originally Posted by Leah

Apologies for the bump, but I'm a little desparate.

Are you looking for a Lookup function for this?

At this point, I'm not sure what I want. The main problem is that I don't know enough VBA to know what has to be changed in the codes to make two similar procedures happen on the same page. Here is what I would like to do:

When someone makes a selection from a drop down list in ProposalsA7, I would like it to fire an event that calls a picture from Pictures, Column C, depending on the selection, and places the picture with the top left corner over C13.

The problem I have is that I already have this exact thing happening further down the page, which creates an error when I try to have both procedures running. So is it simply a matter of renaming something? I don't know what words in the code are VBA words and which are just variables of some sort.

Oh, I also tried using the McGimpsey's solution for this particular event, but the Me.Pictures.Visible = False part made every picture on the page disappear. If anyone knows a way to fix that so that it only hides the pictures I want it to hide, I'll be able to use that procedure instead.

Originally Posted by MSP77079

The attached workbook does what I think you've asked for.

There are 2 sheets ... Proposals and Options. The Options sheet has a list in column A, names of pictures in column B, and pictures with the names shown in column B are hovering over column G.

The range containing the list in column A is Named "List". Not very original, I know.

On the Proposals sheet are 4 cells (highlighted), starting in row 92, with cell validation tied to the List in column A on Options.

When you select from the list, it triggers a macro to run. I will explain how it works. If you want to follow along, right-click on the Proposals tab and select "View Code" from the context menu. Then, scroll to the top of the code window.

The first VBA routine is a subroutine named "Worksheet_Change". Excel causes this routine to "fire" any time any change is made on the worksheet. This is called an "Event" procedure because it responds to an "event". In this case the "event" is any change made on the worksheet. When Excel "fires off" this procedure, it tells VBA which cell was changed. This is called the "Target" range.

We want to ignore most changes. So, the sooner we can decide that the change is something to be ignored, the better. The way I have set up the Proposals sheet, all of the selection cells (the only thing we care about for now) are in column A (column 1). If this is NOT the way you will have your Proposals sheet set up, then delete the line that reads:
If Target.Column > 1 Then GoTo leave

The next thing in this subroutine is a list of the addresses of cells that we are "watching". This example is watching 4 cells. You can see where I put the addresses for these 4 cells. If you have 14 cells you want to watch, then list them accordingly.

Next, we compare the address of "Target" with the list. If any of them match, we call on another subroutine that will pull in the correct (we hope) picture. That other subroutine is named "showPic". The code for it is directly below the "Worksheet_Change" procedure.

We pass to "showPic" the cell that the user changed. For no particular reason, I am calling this "vCell" (validated cell).

The first thing the "showPic" subroutine does is tell Excel to turn off screen updating. This allows us to make changes without Excel needing to re-paint the screen until we finish. That makes the routine run faster and appear a little bit more "magical" to the user.

On the Options page, pictures were named "Picture 1", "Picture 2", et cetera. On the Proposals sheet, any picture can go in any of the validation cell rows. So, we are going to name them for the row number we stick them in.

We do not want two pictures there at the same time; so, the first thing we do is, if we find a picture named for the Row associated with the validation cell (vCell), we delete it.

You will notice that I put in "On Error Resume Next". At some point in the future you will probably want a routine that "cleans up" the Proposals sheet from time-to-time. Which means there might be no picture associated with one or more validation cells. So, trying to delete such a picture would cause an error. That's why I tell VBA to ignore an error at this point.

Next, we define the sheet with the List and the pictures on it; namely, the "Options" sheet. If you ever change the name of the Options sheet, you will need to change this line of code. FYI ... you can change the name of the Proposals sheets any time you want and not have to change the code because we do not use its name anywhere. The word "Me" refers to the Proposals sheet ... but, be careful to have this clear in your mind ... this is only because the code itself is "written on" the "code window" of the Proposals sheet.

The List that appears in the validation cells comes from Column A (column 1) of the Options sheet. So, we want to search this column to find a match with the selection the user made. I defined a variable for this ... "searchRng" ... and set it equal to the Options worksheet's column 1. There should always be a match, of course ... that's what cell validation is all about. But, ya know, users can screw up even our best attempts. So, to be on the safe side, I tell VBA to ignore any errors before attempting to find the match. Then, I use the VBA equivalent of the worksheet function named MATCH. If you're not sure how this works, back in the normal Excel workbook, use Insert >> Function, find the MATCH function from the list, and ask for help. The "searchFor" and "searchRng" are kind of obvious. The third parameter is zero ... which means we are looking for an exact match.

Since I am searching the entire column, the match function will return the row number where the match was found (if one was). If there was no match, we clear the error created and get out of Dodge. If a match was found, we read the name of the picture we want from column B of the Options worksheet.

One of the limitations that has been noted for the mcgimpsey example is that it seems to work fine for a small number of pictures, but fails for a large number of pictures. Not sure why. But, I avoid that issue by referring to "Shape" objects instead of "Picture" objects. Shape is a more general term; Picture is more specific. It would be like referring to Vehicles instead of Trucks.

Instead of changing the Visible property of the picture as mcgimpsey does, we are copying the pictures from the Options sheet to the Proposals sheet. Then aligning it with the validation cell row and Column J (column 10).

The last thing we do is turn back on screen updating so the user can see our wonderful work product.

Hope I've explained this well enough. If not, write back.

Is it possible to position the picture on a different row other then the same row the drop down list is in??

Is it possible to position the picture on a different row other then the same row the drop down list is in??

Thanks for the info