Picture unhide hidden picture

[dujon_uk]

Hi,

I was wondering if someone could help me.
I have a drop down box that contains three options. Depending on the option i want it to show one picture and hide the other two. and when option 2 is slected picture 1 and 3 are hidden and 2 is unhidden. I have wrote some code which does it once, but it fails to unhide hidden pictures.

Please Login or Register  to view this content.

Any help would be much appreciated.

Thanks

[Ron Coderre]

For an alternative non-VBA solution, try this:

Assumption: Pictures are stored on Sheet2 and will be dynamically shown on Sheet1.

Select Sheet2 and turn off Grid Lines
…View…Uncheck: Grid Lines

1)For each picture to be displayed:
1a. Insert.Picture…(select picture and put it in the sheet).
1b. Select the range of cells that is under the picture.
1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text:
Example for a picture of an Elephant:
Formulas.Name_Manager.New
…Name: picElephant
…Refers to: (this is whatever range you've selected under the elephant pic)

2)Build your data validation list on a cell in Sheet1 and pick one of the items.

3)Create a dynamic range name that refers to that cell:
Formulas.Name_Manager.New
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
…or whatever cell you chose tha contains the dropdown list.

EDITED TO CORRECT STEP DESCRIPTIONS FROM THIS POINT FORWARD
4)Copy the range of cells under one of the pictures from Sheet2

5) Select the display cell on Sheet1.

6) Home.Paste.Paste.As_Picture.Picture_Link

7) With the picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic

The picture will be replaced by the picture referred to by the dropdown list.

Each time you select a different item in the list, the associated picture will appear in the picture box and resize appropriately

[jolivanes]

Or, if you'd want a change event, paste code in codesheet for the worksheet in question.

Please Login or Register  to view this content.

Change picture names and cell reference to suit.

Regards
John

[dujon_uk]

@Ron Coderre

Thanks for your help!
I have tried this method but i cant get it to work. I was sure that the VBA code would be easier and simplier but this is not the case atm.

Thanks

[dujon_uk]

@jolivanes

Thanks for this John. I have given this a try but had no joy. What does "Private Sub Worksheet_Change(ByVal Target As Range)" mean?

As when i change my drop down box nothing happens.

Thanks

[jolivanes]

For that code:

Insert / Picture / From File (Select your first picture and move it to where you want it)
Note the name in the box to the left of you formula bar.
Insert / Picture / From File (Select your 2nd picture and move it to where you want it)
Note the name in the box to the left of you formula bar.
Insert / Picture / From File (Select your 3rd picture and move it to where you want it)
Note the name in the box to the left of you formula bar.

Enter the names of the pictures in cells S1, S2 and S3

Select cell Q5
Data / Validation / Allow = List / Source = "=$S$1:$S$3" (or select it with mouse)

Copy the code, right click on the tab of the sheet where the pictures and the drop down list are in, select view code and in the code window that opens up, paste the code in the right, big white space.

Close the code window and select cell Q5. Cycle through the picture names.
You should now have the selected picture showing.

If any more problems, let us know and I'll attach the workbook with small pictures so you can disect it.

HTH
Regards
John

[jolivanes]

See attached file

HTH

John

[dujon_uk]

Thanks for your help, it working nicely now!!

What a great site!!!

Cheers!!