Conditionally inserting image

[Phil]

Hi there,

Does anyone out there know if its possible in Excel (2002)
to conditionally insert an image into a file based upon
the results of formula.

So for instance...

If a2 > 100 then [insert red traffic light image]
Else If a2 > 50 then [insert amber traffic light image]
Else [insert green traffic light image]

Im familiar with VBA and wonder if that holds the solution
but either way Im not sure how to reference each image (&
dont know how each image is stored)?

Any advice greatly appreciated.

Phil

 

[Bob Umlas, Excel MVP]

You don't need an image. You can simply use conditional formatting on the cell and have the cell contain a Wingdings font and enter =CHAR(108), which looks like a solid filled circle. The conditional format would check the contents of A2 and adjust the font color of this cell accordingly

----- Phil wrote: ----

Hi there

Does anyone out there know if its possible in Excel (2002)
to conditionally insert an image into a file based upon
the results of formula.

So for instance..

If a2 > 100 then [insert red traffic light image
Else If a2 > 50 then [insert amber traffic light image
Else [insert green traffic light image

Im familiar with VBA and wonder if that holds the solution
but either way Im not sure how to reference each image (&
dont know how each image is stored)

Any advice greatly appreciated

Phi

 

[Guest]

Hey thanks Bob, thats really useful.

Im hoping that the powers that be here dont require a pre-
determined traffic light image & that this solution will
suffice.

Thanks again

Phil

-----Original Message-----
You don't need an image. You can simply use conditional

formatting on the cell and have the cell contain a
Wingdings font and enter =CHAR(108), which looks like a
solid filled circle. The conditional format would check
the contents of A2 and adjust the font color of this cell
accordingly.

----- Phil wrote: -----

Hi there,

Does anyone out there know if its possible in Excel (2002)
to conditionally insert an image into a file based upon
the results of formula.

So for instance...

If a2 > 100 then [insert red traffic light image]
Else If a2 > 50 then [insert amber traffic light image]
Else [insert green traffic light image]

Im familiar with VBA and wonder if that holds the solution
but either way Im not sure how to reference each image (&
dont know how each image is stored)?

Any advice greatly appreciated.

Phil

.

 

[Dave Peterson]

Another way is to plop 3 pictures of the traffic light in the same position--and
hide the ones that shouldn't be seen and show the one that should.

I called my pictures: pict_red, pict_yellow, pict_green and used this
worksheet_calculate event:

Option Explicit
Private Sub Worksheet_Calculate()

With Me.Range("A2")
If IsNumeric(.Value) = False Then
'do nothing???
Else
'hid them all
Me.Pictures("pict_red").Visible = False
Me.Pictures("pict_yellow").Visible = False
Me.Pictures("pict_green").Visible = False

Select Case .Value
Case Is > 100: Me.Pictures("pict_red").Visible = True
Case Is > 50: Me.Pictures("Pict_yellow").Visible = True
Case Else: Me.Pictures("Pict_green").Visible = True
End Select
End If
End With
End Sub


I searched google images (http://images.google.com) and found lots of different
pictures of traffic lights. You may find some on your local harddrive--cached
versions from visiting different web pages???

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

and for notes about events, you can visit David's page:
http://www.mvps.org/dmcritchie/excel/event.htm

or Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

Hi there,

Does anyone out there know if its possible in Excel (2002)
to conditionally insert an image into a file based upon
the results of formula.

So for instance...

If a2 > 100 then [insert red traffic light image]
Else If a2 > 50 then [insert amber traffic light image]
Else [insert green traffic light image]

Im familiar with VBA and wonder if that holds the solution
but either way Im not sure how to reference each image (&
dont know how each image is stored)?

Any advice greatly appreciated.

Phil