Looking up images

[Guest]

I am building a spreadsheet which has a dropdown menu where the user can select the state they are working with. Depending on the state they select, I would like a .jpeg or .bmp of the state to display in the right hand corner so there is less potential for confusion when users are switching between states. Is there a way to use a function such as the index function to look up an image instead of a value

 

[Dave Peterson]

This technique works ok if you're using xl2k or higher.

Add all 50(?) pictures to a worksheet. Locate each right where you want them to
appear.

Give them nice names. Pict_AL, Pict_AK, ..., pict_WI

Then put the two letter state abbreviations in A1:A50 (in a hidden? worksheet).

Give that range a nice name ("ValidStates").

Add Data|validation to the cell that will get the state abbreviation. Use List
and source of
=ValidStates

Then rightclick on the worksheet tab that should have this behavior and select
view code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Pictures.Visible = False
On Error Resume Next
Me.Pictures("pict_" & Target.Value).Visible = True
If Err.Number <> 0 Then
MsgBox "No picture for " & Target.Value
Err.Clear
End If
On Error GoTo 0
End Sub

======
The bad news is that in xl97, the worksheet_change event won't fire if you put
the data list in a range. (But it will if you put type it in that Source box!)

If you used a dropdown from the Forms toolbar or a combobox from the control
toolbox toolbar, then the code could be modified for that.

 

[Dave Peterson]

Oops. This should have some more checking:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Me.Pictures.Visible = False
On Error Resume Next
Me.Pictures("pict_" & Target.Value).Visible = True
If Err.Number <> 0 Then
MsgBox "No picture for " & Target.Value
Err.Clear
End If
On Error GoTo 0
End Sub


The old version will try to show the pictures for every change in the
worksheet. This version checks to see if the only change was to A1.