Graphic/Picture/Shape return possible?

N

Numb brr Crunched

I would like a graphic, picture, or a shape to be displayed when a value falls within a particular range of numerical scenarios. I'm currently using an IF formula to return a numerical "grade" when values fall within a particular range, but would like to make the look a little more interesting by using something fun to look at.

Any help is appreciated!
 
D

Dave Peterson

You could use a worksheet event. And since it's the result of an =If()
statement, you'd want to use the worksheet_calculate event.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Dim OldVal As Variant
Private Sub Worksheet_Calculate()

Dim myPictName As String
Dim myCell As Range

Set myCell = Me.Range("a1")

If IsEmpty(OldVal) Then
OldVal = myCell.Value
Else
If Me.Range("a1").Value = OldVal Then
Exit Sub 'nothing changed
Else
OldVal = myCell.Value
End If
End If

Me.Pictures.Visible = False

Select Case OldVal
Case 1 To 3: myPictName = "bill"
Case 4 To 9: myPictName = "jim"
Case Else: myPictName = ""
End Select

If myPictName = "" Then
'do nothing
Else
Me.Pictures(myPictName).Visible = True
End If

End Sub

Adjust the names to match your pictures and the range to check (I used A1).
 
Top