Multiple picture inserts via dropdown lists

[Wackyracer]

I have a workbook that has several dropdown menus. One of these menus is
linked so that a cell displays a picture depending upon the answer in the
dropdown list. To achieve this I used this site provided in another answer on
this site.

http://www.mcgimpsey.com/excel/lookuppics.html

Now to my problem. I need to reproduce this for other dropdowns on the same
sheet. I tried copying the VB code and adjusting the cell ref but this gives
me an error message and neither picture appears. I assume I need to add
something to the VB code but not being well versed in this area I stuck.

EG Dropdown list 1
Coresponding Picture 1
Dropdown list 2
Coresponding Picture 2
Dropdown list 3
Coresponding Picture 3

Can anyone help?

 

[Gord Dibben]

Got to Debra Dalgeish's site for a sample workbook from Bernie Dietrick for
adding pictures to selections.

Alows for more than one picture to be displayed based upon a value or item
chosen from a DV list

http://www.contextures.on.ca/excelfiles.html#DataVal

DV0049 - ClipArt Selection

Edit the code to suit.


Gord Dibben MS Excel MVP

 

[Ken Johnson]

I have a workbook that has several dropdown menus. One of these menus is
linked so that a cell displays a picture depending upon the answer in the
dropdown list. To achieve this I used this site provided in another answer on
this site.

http://www.mcgimpsey.com/excel/lookuppics.html

Now to my problem. I need to reproduce this for other dropdowns on the same
sheet. I tried copying the VB code and adjusting the cell ref but this gives
me an error message and neither picture appears. I assume I need to add
something to the VB code but not being well versed in this area I stuck.

EG Dropdown list 1
Coresponding Picture 1
Dropdown list 2
Coresponding Picture 2
Dropdown list 3
Coresponding Picture 3

Can anyone help?

Here's another site you could try...

http://www.contextures.on.ca/excelfiles.html#DataVal

Or, the following code uses lookuppics code, that you referred to, for
3 dropdowns and 12 pics (4 separate pics for each of the dropdowns).
The code first adds each of the 12 pics to one of three new
collections. Once the pics are in their appropriate new collection,
the code basically does the same as the lookuppics code for each new
collection separately instead of all the pics on the worksheet in one
go.

You may be able to amend this code to suit your worksheet.

If you email me I can send a working copy (kencjohnsonatgmaildotcom,
change the at and dot to correct characters)

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim oPic As Picture
Dim ncPix1 As New Collection
Dim ncPix2 As New Collection
Dim ncPix3 As New Collection
Dim Coll As Collection
Dim I As Long
For Each oPic In Me.Pictures
Select Case oPic.Name
Case "Picture 1", "Picture 2", _
"Picture 3", "Picture 4"
ncPix1.Add Item:=oPic
Case "Picture 5", "Picture 6", _
"Picture 7", "Picture 8"
ncPix2.Add Item:=oPic
Case "Picture 9", "Picture 10", _
"Picture 11", "Picture 12"
ncPix3.Add Item:=oPic
End Select
Next oPic
Me.Pictures.Visible = False
For I = 3 To 9 Step 3

Select Case I
Case 3
Set Coll = ncPix1
Case 6
Set Coll = ncPix2
Case 9
Set Coll = ncPix3
End Select
With Cells(1, I)
For Each oPic In Coll
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
Next I
End Sub

Ken Johnson