changing image based on pulldown

[Sandy Ryan]

i have a pulldown - and need to change the image displayed on the form, based
on the selected item... i have the image control, but can't find how to
program it based on the pulldown.
More info -- i have a validation data sheet that has the pulldown values in
one column and the location of the file in another column
thanks
for the help

 

[sebastienm]

Hi,
First, say your image control is Image1 and your drop dow is Combobox1.

'To capture the user change of choice use the Combobox1_Click sub:
Private Sub ComboBox1_Click()
Dim i As Long
i = ComboBox1.ListIndex 'user choice
msgbox "the user has just changed to choice " & i
End Sub

Now to load images when the user changes choice: 2 Methods

'1 -- LoadPicture which requires the user to have the pictures on the hard
drive.
Private Sub ComboBox1_Click()
Dim i As Long
i = ComboBox1.ListIndex 'user choice
If i = 0 Then 'if 1 then this image
Image1.Picture = LoadPicture("C:\Program Files\Microsoft
Office\Office10\Bitmaps\Styles\ACBLUPRT.GIF")
ElseIf i = 1 Then 'if 2 then this image
Image1.Picture = LoadPicture("C:\Program Files\Microsoft
Office\Office10\Bitmaps\Styles\ACSUMIPT.GIF")
Else
End If
End Sub

'2 -- ImageList control 'Much Easier i think
You can use the ImageList control to load pictures into your Image control.
- Add a ImageList control:
From the toolbox, right-click and choose 'Additional Controls', browse to
the 'Microsoft ImageList Control' latest version (probably SP6)
Now the control is added to the Toolbox
Add one to the Userform
- Load pictures into the ImageList control
in the properties for the image list control, select Custom. The
Properties form of the ImageList pops up. In the Images tab, load each
picture you want to use in the order they would match the items in the
drop-down. Then close the dialog box.
- now you can use the Imalist control as follow:
Private Sub ComboBox1_Click()
Dim i As Long
i = ComboBox1.ListIndex 'user choice
Image1.Picture = ImageList1.ListImages(i + 1).Picture
End Sub

 

[K Dales]

Here is a fairly easy way to do it:
1) Use the drawing toolbar to make a rectangle on the worksheet. Just a
regular, plain rectangle.
2) Use the forms toolbar to add the combobox. Set it so the data range is
your list of values. Set the linked cell, location not important. I will
say for this example that it is in C1.
3) In another cell create a formula to look up the file path. The Offset
formula would be good; e.g. if your list begins on Sheet2, cell A1 then your
formula to look up the file path from column B in Sheet2 would be this:
=OFFSET(Sheet2!A1,C1-1,1)
where C1 is the linked cell from the combobox. I will say that this formula
is in cell D1.
4) Now assign this simple macro to the combobox for combobox_change() using
the cell where you looked up the file path to set the fill picture for the
rectangle:

Sub DropDown1_Change()
ActiveSheet.Shapes("Rectangle 1").Fill.UserPicture(Range("D1").Text)
End Sub

This is pretty simple and works nicely; the only difficulty would be dealing
with pictures that vary in aspect ratio (height to width ratio); then you
need either a more sophisticated image control or you need to add code to
determine the aspect ratio of the picture and adjust the rectangle
accordingly.

 

[Sandy Ryan]

Sebastien
thanks for the insight - the dropdown list that i'm using is via data
validation - how do i figure out the name to see if this will solve me
problem... or do i reference it via cell (B3)?

 

[sebastienm]

I see; i thought you were working in a Userform.

You can get the index of the choice within the list source (1,2,...) and use
this index as the index in the ListImage:
'-----------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rgDropDown As Range, rgListSource As Range
Dim i As Long

Set rgDropDown = Range("D3")
Set rgListSource = Range("ListSource")

If Not Application.Intersect(Target, rgDropDown) Is Nothing Then
Application.EnableEvents = False
i = Application.WorksheetFunction.Match(rgDropDown.Value,
rgListSource, 0)
Image1.Picture = ImageList1.ListImages(i + 1).Picture
Application.EnableEvents = True
End If
End Sub
'------------------------------------------------------------

Would that work?
Also, check at K Dales' solution, it may work for you.