Looking up images

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.
 
Back
Top