I have prepared a code below to display one of 3 different pictures to
show high or fair or low performance. This code works fine for one
condition input which is at cell A6.

The problem is I have another 15-20 independent conditions (cells)
with the same choices: high, fair or low performance and depending on
the user selection, the same set of pictures are used again for
another condition input. I know the "not so smart" solution is to
multiply the 3 pictures, so I have 45-60 pictures for all conditions
and add more "if and case" lines.

The hidden/visible technique may not be appropriate for my purpose, so
I'm open for any suggestion. Please let me know how I can use the same
set of pictures to show the selected condition and use the same set
over and over again for another condition. Note, I need the pictures
in the same workbook as there are many users will be using the file.

Thanks in advance for your help.

Yokie

====code to show a picture based on condition in one cell =======

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("A6")) Is Nothing Then

Me.Pictures.Visible = msoFalse

Select Case Target.Value
Case "High Performance"
Shapes("Picture 1").Visible = msoTrue
Case "Fair Performance"
Shapes("Picture 2").Visible = msoTrue
Case "Low Performance"
Shapes("Picture 3").Visible = msoTrue
Case Else
End Select

End If
End Sub

I had a similar request........wanted pics to change when DV selection
made........ and received help from Bernie Dietrick with some code and a sample
workbook he emailed to me.

You will need just the 3 pics stored in the workbook which can be re-used at
each selection.

If you like I can email Bernie's workbook to you for your perusal.


Gord Dibben MS Excel MVP


On 23 Feb 2007 10:03:42 -0800, wrote:

I have prepared a code below to display one of 3 different pictures to
show high or fair or low performance. This code works fine for one
condition input which is at cell A6.

The problem is I have another 15-20 independent conditions (cells)
with the same choices: high, fair or low performance and depending on
the user selection, the same set of pictures are used again for
another condition input. I know the "not so smart" solution is to
multiply the 3 pictures, so I have 45-60 pictures for all conditions
and add more "if and case" lines.

The hidden/visible technique may not be appropriate for my purpose, so
I'm open for any suggestion. Please let me know how I can use the same
set of pictures to show the selected condition and use the same set
over and over again for another condition. Note, I need the pictures
in the same workbook as there are many users will be using the file.

Thanks in advance for your help.

Yokie

====code to show a picture based on condition in one cell =======

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("A6")) Is Nothing Then

Me.Pictures.Visible = msoFalse

Select Case Target.Value
Case "High Performance"
Shapes("Picture 1").Visible = msoTrue
Case "Fair Performance"
Shapes("Picture 2").Visible = msoTrue
Case "Low Performance"
Shapes("Picture 3").Visible = msoTrue
Case Else
End Select

End If
End Sub

Soem ideas.

1) The sub routine gets cell that gets changed. Base on the cell addrress
you may wantt to associated a range of cells to certaion pictures.
2) You mayu want to have multiple worksheets, and havve different pictures
associatted with each worksheet. You can check the name of the active
worksheet to determine which pictures are used
3) Have the picture names in the 3 columns to the right of the cell that
gets changed. The subrouting can check the cell that got changed, then look
up the 3 cells to the right of the changed cell.

" wrote:

I have prepared a code below to display one of 3 different pictures to
show high or fair or low performance. This code works fine for one
condition input which is at cell A6.

The problem is I have another 15-20 independent conditions (cells)
with the same choices: high, fair or low performance and depending on
the user selection, the same set of pictures are used again for
another condition input. I know the "not so smart" solution is to
multiply the 3 pictures, so I have 45-60 pictures for all conditions
and add more "if and case" lines.

The hidden/visible technique may not be appropriate for my purpose, so
I'm open for any suggestion. Please let me know how I can use the same
set of pictures to show the selected condition and use the same set
over and over again for another condition. Note, I need the pictures
in the same workbook as there are many users will be using the file.

Thanks in advance for your help.

Yokie

====code to show a picture based on condition in one cell =======

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells, Range("A6")) Is Nothing Then

Me.Pictures.Visible = msoFalse

Select Case Target.Value
Case "High Performance"
Shapes("Picture 1").Visible = msoTrue
Case "Fair Performance"
Shapes("Picture 2").Visible = msoTrue
Case "Low Performance"
Shapes("Picture 3").Visible = msoTrue
Case Else
End Select

End If
End Sub

Hi Gord,

I just sent a PM to you with my email address to you. I'll appreciate
if you can send Bernie's code and the workbook. Thanks in advance.

Yokie