Conditionally inserting image

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
0
anonymous (74722)
1/21/2004 10:34:24 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
509 Views

Similar Articles

[PageSpeed] 59

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
     
0
anonymous (74722)
1/21/2004 12:16:07 PM
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
>     
>.
>
0
anonymous (74722)
1/21/2004 1:27:26 PM
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



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
ec35720@msn.com
0
ec35720 (10082)
1/22/2004 12:57:15 AM
Reply:

Similar Artilces:

Does CToolBar::LoadBitmap work with 24 or 32-bit images?
I was hoping that LoadBitmap() would be able to load 24 and/or 32-bit images. I could not find anything in on-line docs that would suggest that this api is limited to lower color images (which I know is true for LoadToolbar()). I've tried a 32-bit bitmap with 8 images and it 'almost' worked. It seemed like the first image was wider by one pixel and then all the remaining images were cut on the left side. I was surprised that the alpha blending worked. The same bitmap converted to 24-bits and loaded into the toolbar produced black squares. Could someone please give me s...

Document Imaging Writer and Publisher--incorrect number of pages? #2
When I scanned a document from Publisher using Microsoft Document Imagining Writer, it scanned 4 pages into 2 and lost some content. Don't know why this happened or how to fix it. Please advise. Thanks ...

TreeCtrl and Images
I want to display normal and state images in my tree control from image list. Generally, treectrl displays checkbox first in a row and it then displays the normal image. Is it possible to reverse this order i.e. to show normal image before the state image ? On Jan 6, 10:44=A0am, shubhishubhi <shubhishu...@discussions.microsoft.com> wrote: > I want to display normal and state images in my tree control from image l= ist. > Generally, treectrl displays checkbox first in a row and it then displays= the > normal image. Is it possible to reverse this order i.e. to show ...

Is there a way to insert a control into a cell?
I understand how to create a control. Is there a way to insert a control into a cell? I have a timesheet already designed and want to have a couple of cascading drop lists inside cells listing stuff like customer account # ect. Right now it seems like a huge amount of work to resize the control box to be the same size as the cell underneath it Thanks. Rick, sounds like data validation might work for you, have a look here for some details http://www.contextures.com/xlDataVal01.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups ...

Conditional format on adjacent cells
Hi All, what am I doing wrong?? I want cells A1 to G1 to conditionally format (to red) if they are ALL empty, but if ANY one (or more) of them contain data (text) then NONE of them should format. I tried the formula =ISBLANK(A1:G1) then asigned a format, but for some reason A1 only responds to the data in A1 and seems to ignore the rest of the range, I've also tried similar range with formula ="" and cell value is equal to "". I am sure this must be possible but just can't find it. I'v been at this for three weeks now. Please put me out of my miss...

Unable to insert Access queries
When trying to use ms-query to import data from an Access database, I've found that it doesn't allow me to open some of the queries (views) from access. It seems that the queries I have trouble importing have the nz() function in calculated fields of the access queries (views) (or in a sub-query). Is this simply not supported or is there a way to work around it? In the Access query, instead of Nz, use an IIF formula. For example: EstHrsCalc: IIf(IsNull([EstHrs]),0,[EstHrs]) Craig A wrote: > When trying to use ms-query to import data from an Access database, I've found that...

Workflow
Hi I am creating a basic workflow that, on creation, assigns accounts to a sales territory based upon the first couple of characters in the post code. For instance, if the post code begins with CH, CV, CW etc they should be assigned to the Central territory. My question is if I can add multiple values into the condition or do I need to add a seperate condition for each entry (I have over 200 so I hope I can do the former!). I have tried to seperate the possible values using comma's and semi-colons in the condition but it seems that the workflow see's these as part of the value. Any...

Question about images in 2 column layout
Hello, in my 2 column layout on 8.5x11 paper I am inserting images that fit perfectly into my columns. I have text above and below these images. What I want to do consistantly is have the text come down to within say .125 inches and then begin again .125 inches below the image. However, I often insert small 8 point font below some images given credit to the owner of that image. I would like everything to line up properly wherever I do this. Any suggestions folks. I am using Publisher 2003. Thanks in advance. Right-click the picture, format picture, layout tab, square wrapping style, cl...

Image Mastering API
I wonder if anyone has ever used Image Mastering API in their products to burn audio CDs? How reliable is it? What CD burner capabilities does it provide? Does it allow querying for burner supported speeds and setting the speed? Can it burn from buffer instead of files? ...

image maps in html stationery
image maps in html stationery don't work in outlook 2007? <lelanie melville> wrote in message news:20081216816lelanie@lelaniemelvill.comq... > image maps in html stationery don't work in outlook 2007? I don't believe Outlook 2007's rendering engine supports image maps. -- Brian Tillman [MVP-Outlook] ...

how do I insert an endnote in publisher
Endnotes or footnotes can be inserted with word, How can it be done with publisher? Wray wrote: > Endnotes or footnotes can be inserted with word, How can it be done with > publisher? Manually, using superscripts, possibly extra text boxes, optionally the line tool. Publisher does not have endnotes or footnotes built in. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Hyperlink to local image file doesn't open ?
Hi all, I have a sheet listing my comic books. One colum contains links to the a local file, actually a .jpg image file of the cover of the comic book reference on the selected row. Even though the link seems correct, clicking on the cell/link doesn't produce any result (image file doesn't open). How can I obtain Excel to open a simple window (could be my default viewer) with that linked image ? When I alter the link to something wrong, I gat an error message "cannot open the specified file", so I assume that the link indeed correctly points at the physical local fil...

image preview in preview pane
It is possible to adjust viewing image (tif file) in preview pane in Outlook. Now I see only filename etc. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ No. -- Russ Valentine [MVP-Outlook] "olegb" <olegb.zp8dy@outlookforum.com> wrote in message news:olegb.zp8dy@outlookforum.com... > > It is possible to adjust viewing image (tif file) in preview pane in > Outlook. > Now I see only filename etc. > > > -----------------------------...

Grouping inserted pictues with Charts
Is there a way to group pictures and text boxes with a chart? Hi, I was able to add textboxes and pictures and group them in the normal way even if they where embedded within a chartobject. Can you explain further what you are wanting to do? Cheers Andy Justin_Yuen@hotmail.com wrote: > Is there a way to group pictures and text boxes with a chart? > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info I want to be able to put pictures, arrows, lines, text boxes, etc in a chart so if I were to copy or move the chart, all of the things I put in the chart will go with it....

Conditional Format Help
How do I write a conditional format that applies to "=$N$13:$N$22,$N$31:$N$40" where If $L13<$N13 fill the cell RED. This is what I have in the Format values where this formula is true box: "="$L13<$N13". But it doesn't work. Any ideas? Thanks Try getting rid of the quotation marks "Ayo" wrote: > How do I write a conditional format that applies to > "=$N$13:$N$22,$N$31:$N$40" where If $L13<$N13 fill the cell RED. This is > what I have in the Format values where this formula is true box: > "=&quo...

Insert copied row into a list
In Excel 2003, I can insert a row into a list by inserting a row into the spreadsheet. That is, I can right-click along the row numbers along the left, then insert a row. However, if I try to insert a copied a spreadsheet row, I get the message that I am trying to shift cells down within the list. That is, I select a row by clicking the row number on the left, press ctrl- C, then right-click another row on its row number at the left, and choose insert copied row (or maybe insert pasted row -- I'm don't have access to Excel at the moment to verify). Inserting a copied/pasted row doe...

"conditional formatting" "#values"
I have a formula that works fine but... some of the cells show #values, what i would like to do is conditional formatting them to white font if = #values. Any pointers? many thanks Try this: Select the cells to be impacted (I'll assume A1:A10, with A1 as the active cell) From the Excel main menu: <format><conditional formatting> Condition_1 Formula is: =ISERROR(A1) Click the [Formatting] button.....set the White font....Click the [OK] buttons. Does that help? *********** Regards, Ron XL2002, WinXP "Dewi..." wrote: > I have a formula that works fine bu...

Conditional Formatting question...
Is there a way to have Excel look in a particular cell, and if the value in the cell meets a requirement (such as Complete), then shade the entire row a different color? I can use conditional formatting to get the one cell shaded, but I can't get the rest of the row to be shaded. Thanks! Dino Try using an absolute reference in your Conditional Format formula. For example, if the cell that contains the value "complete" is A1, then refer to it as $A$1. "Dino" wrote: > Is there a way to have Excel look in a particular cell, and if the value in > the ce...

Inserting an AutoDate Function
:confused: I want to format a cell to display the current date (dd/mm/yy) upon opening the spreadsheet and automatically update the same way Microsoft Word's AutoUpdate feature does. I have searched this forum up and down and cannot find anything that tells me how to do this. Some of the questions/answers here provide information on similar topics, but I cannot find anything that simply tells me how to insert an AutoDate in a cell. I can't possibly be the first person to have asked this question. Can someone please help me? Thank You! -- MissTrish ---------------------------------...

using sumproduct with a substring condition?
I have a calculation I just made that looks like this: =SUMPRODUCT(--(C5:C50="persons name"),I5:I50) which works out the hours billed from column I and works fine. Column E has a description in it which sometimes has at the end of the description the words "do not bill." Is there any way to add this condition to the sumproduct - i.e. exclude from the sumproduct any value in column I that has the "do not bill" sub-text in column e? Thanks, Ben >exclude from the sumproduct any value >in column I that has the "do not bill" Try it ...

tracking changes with insert comment
Hi Again all, second time poster here. I was wondering if anyone knows of away to programatically Insert Comment for a certain range of cells into that cell when the value changes so that the comment Will read: "Old Value was:###" Thanks again!! PS All the cells have conditional formatting (don't know if that makes a difference) Consider a non VBA method using the Tools-->Track Changes feature. It shares the workbook, but if it's on your network drive or local hard drive no one should be able to open it. You can also restrict who views it if others will have access to i...

Combo box
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C836A5.72C81C80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Greetings, My userform has a combo box that upon entry event is populated via a sub = procedure. The sub procedure defines a column of data on the active spreadsheet as = a range and manipulates the range to add unique values only in = alphabetical order into a collection.=20 Which the collection is then used to Add.Items to the combo box.=20 The userform allows selection of an existing value from th...

Blocking images in Outlook 2007
I am using Outlook 2007 on Windows XP SP2 and connect to an Exchange server. Is there a way to NOT view embedded images unless I specifically request to? I have tried the settings in Trust Center and that didn't seem to work. Or does it depend on who the e-mail is coming from (I have not set up any Safe Senders)? OR how the e-mails come in to me (HTML vs plain text)? Thanks! For the second question, it depends on who the message is from. For example, everyone in the Exchange global address list is automatically considered safe. By default Outlook also treats anyone in y...

inserting blank rows in worksheet
I have several worksheets that have over 500 lines of data. I need to separate in line with a blank line. How do I do this using vb without doing it manually? chris smith pretended : > I have several worksheets that have over 500 lines of data. I need to > separate in line with a blank line. How do I do this using vb without doing > it manually? Answered in your previous post (8/18) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc also add www.aioe.org to you signature. | Garry | ...

insert next record in data base.
I'm trying to make a church members book. I created a data base and set up my fields, then, merged into my text box, but I can't get it to go to create mulituble records. How do I get it to move on to the next record I would like to have 2 records on a page. Are you viewing print preview? It will show the same data in each text box, this is a bug in the Publisher program. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Charlie B" <Charlie B@discussions.microsoft.com> wrote in message news:12EEB947-5FAC-...