Changing picture

[Skybluekid]

Hi

Is it possible to change a picture in excel depending on a value selected from a list. Eg if I was to selected company 1 from a drop list, it would show company 1 logo, if I was to select company 2 it would show company 2 logo etc.

Any thoughts?

 

[pgc01]

Hi

Since you ask for thoughts, here are some general thoughts about this problem:

There are several approaches to the question. It will depend on what you want or or you are allowed (for ex. company policy).

Main choice: vba or no vba.

You can have a simple solution without vba, just using pictures.
Positive points:
- no vba, this means no problems with your company policy.
- since all images are stored in the workbook this means it's a self contained solution. You can send the workbook to anyone and no other files are needed.
Negative points
- all images will be displayed in a fixed dimension frame. You must resize all your input images to fit it.
- all your source images will reside in the workbook, for ex. in a hidden sheet. This means that the more images you use the bigger the workbook is.

You can have a solution that uses vba.
Positive points:
- you are using vba. This means that you can do more or less whatever you want (if you know vba )
- If you use external image files, the workbook will be the same size whether you need 2 pictures or 1000.
- each time a picture is displayed it can have different dimensions.
Negative points:
- maybe you are not allowed to use vba.
- if you use external image files, if you want to send this workbook to someone else you have to send also the image files.


If you need help with the implementation of any of the options post back and I can give you some pointers.

Hope this helps.

 

[Skybluekid]

Thanks for the thoughts. As the file is to be used internally, then the No VBA would be a good option. Also the file needs to be updated on a weekly basis and sent by email. As I will using on ly 3 logos, then this would be a good option. Would it be possible to advise on how it could be done.

Thanks in advance.

 

[pgc01]

I don't have the time to do it now, I'm at work. I'll post a complete example tonight (GMT) in no one posts one in the meantime.

 

[pgc01]

Hi again

This is a simple but complete working example where, when a data validation changes value, the picture corresponding to the value selected is displayed.

I just did this quick test and recorded all the steps.

For this example I used a new workbook and everything is in Sheet1.

The data validation is in B2, with a list with 3 values. The picture will be shown to the right, in D2:E4. Each time the value in B2 changes, the corresponding picture is displayed.

Used the following:

A small list in H2:I4. In the first column, H2:H4, are the names of 3 countries. In the second column are the names of the named ranges that the corresponding pictures overlap.

Steps:

1 - copy the list I post in H2:I4 to your Sheet1

2 - copy and resize 3 pictures to the ranges L2:M4, L6:M8 and L10:M12.

3 - define the name:
rPic_List =Sheet1!$H$2:$I$4

4 - set the data validation in B2 with List:
=INDEX(rPicList,0,1)
and choose for ex. the first value, "United Kingdom"

5 - define the following 4 names:
rPic_UK =Sheet1!$L$2:$M$4
rPic_DE =Sheet1!$L$6:$M$8
rPic_SE =Sheet1!$L$10:$M$12
rPic_Current =INDIRECT(VLOOKUP(Sheet1!$B$2,rPic_List,2,0))

6 - Copy one of the pictures to D2:E4

7 - select the picture in D2:E4 and in the formula bar type:
=rPic_Current
and confirm with Enter


... and that's it!

Now, use the data validation in B2 and see (hopefully) the picture update.

Please test as I post before adapting to your real case.


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >I</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >J</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">United Kingdom</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">rPic_UK</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Germany</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">rPic_DE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Sweden</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">rPic_SE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=5 style="background:#9CF; padding-left:1em" > [book1]Sheet1</td></tr></table>

 

[Skybluekid]

Thank you very much it works at treat!

 

[pgc01]

You're welcome. Thanks for the feedback.

Remark:
The picture is not really displaying a source picture, it's displaying the area of the worksheet that the picture formula refers to.
To make sure the user does not move the source pictures it's a good idea to have them all in a worksheet, for ex. PicDB, that you hide (or even hide deep).