Macro Image Help

[slamups21]

I posted this a week or so ago but I havent heard back in a while after some intitial good feedback so I thought I would re-post.

General Idea: I want Excel to call up an image to match a series of cells I have created. The logic is this: a user selects from 6 Yes/No fields, and depending on their combination, I want a specific image that is in a different workbook to appear.For example a chain of Yes/Yes/No should bring up a different image than Yes/No/Yes. The list of combinations will not be including duplicates, so 2+3 and 3+2 would bring up the same image.

The following code and workbook is how far I have gotten, and there remain two problems I would like to fix:

1) The worksheet only reacts and pastes a picture once the user changes cell C8. Can we manipulate this so each of the dropdown cells C4-C8, will paste an appropriate picture if changed?
2) Each click of the C8 dropdown cell pastes the image on top of already pasted images. It gets in the way of the worksheet, it would be best if one click made the picture appear and another would not make a change unless it is to bring up another picture

Please Login or Register  to view this content.

[Chance2]

Is it important to use code for this?

This can be done with named ranges and the CHOOSE command (as one method).
I aligned your images with cells so I could reference them, then I copied a cell (does not matter which) and pasted as a picture link (under the paste menu). I then made a named range called MyPic and added my choose formula (you may want to use something else depending on your final product). Finally I changed the link (in the address bar) of my pasted picture link (from step one) to the MyPic reference...

Note: The paste as picture link is a little more difficult to find in xl 2003, but it is there (I think you need to shift while selecting the Edit menu)

[slamups21]

wow this looks perfect! I have no experience at all with the choose command and am finding it a little difficult to backtrack from your example sheet but I hope I will figure it out. How does it know what combination of Yes/No to use with which image? This looks brilliant I just gotta figure it out im glad its possible without code

[royUK]

You should continue in your original post if it hasn't been solved

[slamups21]

The answer you provided was good, but Im finding a problem I cant get around. The choose function is spitting out a picture based on the number of "Yes" the user selects. This presents a problem,however, since I need a different image for different combinations, even if they have the same number of Yes. For example if cell 1 and 2 are Yes, or 1 and 3 are Yes, I would want two different pictures, but the function you provided would yield the same pic for both. Is there a way to do a nested if function within the choose?

[shg]

Perhaps ...

Please Login or Register  to view this content.

[Chance2]

Still avoiding code, you will need a table of combinations and picture locations.
I have added the table and modified MyPic. I used another name for the combinations (MyChoice)...

[slamups21]

This worksheet looks much better, do you mind going a little more detailed as to how I would re-create this with my actual data? I am fine up until the point of creating the MyPic but I dont know where the MyChoice comes in and how that works

[Chance2]

MyChoice is just a series of concantinated IF statements to get your unique combinations.

Each IF is just checking for Yes and placing a number or null based upon the results.

[slamups21]

Ok cool, I guess the part still confusing is how are MyChoice and MyPic linked? Do I assign MyChoice to a specific cell?

[Chance2]

MyChoice returns a combination from the questions from Testing (C4 - C8).
C4 = 1, C5 = 2, C6 = 3 and so on (if you have more than 5 in your final sheet).
Examples:
C4 = Yes and C5 = Yes and C6 = No then MyChoice = 12
C4 = Yes and C5 = No and Yes = No then MyChoice = 13
C4 = No and C5 = Yes and Yes = No then MyChoice = 23

MyPic uses MyChoice (Combination column E) to find the location of the picture (Range column F) on Sheet1. The "Range" represents the cells behind the pictures, so you can have as many combinations as pictures. in this case I just randomly assigned combinations to pictures.

[slamups21]

Thanks so much for all the help I have it up and running now, it is exactly what I was looking for!