Conditional Picture?

Does anybody knows how to insert one picture that changes without use
intervention everytime the result of another cell changes?
eg.: If the cell A1="Alex" the cell A2=Picture of Alex. Then if 
change the cell A1 to "Mike" the cell A2 displays the picture of Mike

--
Message posted from http://www.ExcelForum.com

0
12/26/2003 1:28:59 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
640 Views

Similar Articles

[PageSpeed] 41

Hi Alex

This macro example from this group a long time ago you can use.
The jpg files must have the same names as the number you type in cell "a1"
Change the path to the files in the code.

Place the code in the Sheet module

Right click on a sheet tab and choose view code
Paste the code there
Alt-Q to go back to Excel


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim rngProducts As Range
    Dim pic As Picture, shp As Shape
    Dim szInvalids As String

    On Error Resume Next
    'Only insert the picture if it's in the area where they type the Product Names
    'Change "a1" to a range of cells where they'll be typing in Product numbers
    Set rngProducts = Intersect(Me.Range("a1"), Target)
    On Error GoTo 0

    If Not rngProducts Is Nothing Then  'They entered a product number
        'Loop through each cell they entered in
        '   in case they copied several product numbers into several cells
        For Each rng In rngProducts
            'Remove the exisitng picture (shape) from the cell to the right
            For Each shp In Me.Shapes
                If shp.TopLeftCell.Address = rng.Offset(0, 1).Address _
                Then shp.Delete
            Next shp
            'Insert the picture
            On Error Resume Next
            Set pic = ActiveSheet.Pictures.Insert("C:\Documents and Settings\Ron\MyFiles\" _
            & rng.Text & ".jpg")
            On Error GoTo 0
            If Not pic Is Nothing Then  'The picture exists
                With pic
                    .Height = rng.Offset(0, 1).Height
                    .Width = rng.Offset(0, 1).Width
                    .Left = rng.Offset(0, 1).Left
                    .Top = rng.Offset(0, 1).Top
                End With
            Else    'Invalid entry, add it to the list of invalids
                szInvalids = szInvalids & rng.Address & ": " & rng.Text & vbLf
            End If
        Next rng

        'Show them the invalid entries if there wer any
        If Len(szInvalids) Then
            szInvalids = "The following were either invalid product entries or " & vbLf _
            & "the product's image could not be found:" & vbLf & vbLf & szInvalids
            MsgBox szInvalids, vbExclamation
        End If
    End If
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"AlexCdeC" <AlexCdeC.z1qjy@excelforum-nospam.com> wrote in message news:AlexCdeC.z1qjy@excelforum-nospam.com...
> Does anybody knows how to insert one picture that changes without user
> intervention everytime the result of another cell changes?
> eg.: If the cell A1="Alex" the cell A2=Picture of Alex. Then if I
> change the cell A1 to "Mike" the cell A2 displays the picture of Mike.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
12/26/2003 3:09:40 PM
One way:

Add all the pictures you want to the worksheet.  Name them Pict_Alex, Pict_Mike,
Pict_namehere.

Put them all where you want them to show up (probably directly on top of each
other????).

Then right click on the worksheet tab that holds the pictures and select view
code.  Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myPict As Picture

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

    For Each myPict In Me.Pictures
        myPict.Visible = CBool(StrComp(myPict.Name, _
                            "pict_" & Target.Value, vbTextCompare) = 0)
    Next myPict

End Sub

When you type in the name in A1, this worksheet event will fire and the code
will hide all the pictures whose name doesn't match pict_valueinA1.

A tip for renaming the pictures.  Select each and type the new name in the
namebox--to the left of the formula bar.  Remember to hit enter when you're done
typing.



AlexCdeC wrote:
> 
> Does anybody knows how to insert one picture that changes without user
> intervention everytime the result of another cell changes?
> eg.: If the cell A1="Alex" the cell A2=Picture of Alex. Then if I
> change the cell A1 to "Mike" the cell A2 displays the picture of Mike.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/26/2003 3:21:24 PM
Reply:

Similar Artilces:

conditional formatting #15
I have applied conditional formatting to a cell on a form that is printed on a black and white printer so the formatting is somewhat ruined as you cannot distinguish between the shades of grey. If I try and use =IF(J10>=3,"Critical","Ok") it of course returns a circular reference error as it is referring to itself. Is there a way to achive this perhaps in VBA so that I can apply the logical rules but return a text string. regards in advance Peter Instead of colours, you could use a pattern from the Pattern dropdown in the conditional formatting Patterns tab. ...

Conditional formatting #15
This is a multi-part message in MIME format. ------=_NextPart_000_001C_01C85F65.D08175E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a date in cell A1, How do I conditionally fill cell G5 with a = fill color? Thank for any assistance, Ben ------=_NextPart_000_001C_01C85F65.D08175E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3...

Conditional Format #20
I see that I can only use 3 conditional formats. I need a few more than 3 is there any way to do this. Kevin Try this free add-in on Bob Phillips' website: http://www.xldynamic.com/source/xld.CFPlus.Download.html Another possibility (albeit limited) is to combine your conditional formats with custom formatting. See J.E. McGimpsey's site for an example: http://www.mcgimpsey.com/excel/conditional6.html HTH Jason Atlanta, GA >-----Original Message----- >I see that I can only use 3 conditional formats. I need a few more than 3 is >there any way to do this. > >Kev...

Excel bug: conditional formatting
When using conditional formatting with more than more formula, the formatting ceases to go through all conditions once one condition becomes true. This isn't the same behavior as when cell values are used. This to me is a bug, but if this is intended, it should at least be an option to continue through all conditions. The behaviour is the same when cell values are used. For example: Condition 1: Cell Value Is greater than 10 Format: Bold Condition 2: Cell Value Is greater than 20 Format: Red Condition 3: Cell Value Is greater than 30 Format: Italic A cell that contains a...

pictures not showing
And constant notices when replying to emails that a picture is not showing up. I think my settings are correct. This problem began a few days ago--until then I could see pics. Is it a msft update problem? sue xp pro; sp2, ie7, oe6.2900,2180 (wish i could copy all that) It's not an update. You haven't even installed Service Pack 3 yet. Are you having trouble receiving and sending pix? Make sure you are sending and reading in HTML and not plain text. Tools | Options | Send | HTML Settings. Check: Send pictures with messages. Send a pix to yourself. Do ...

Inserted pictures not seen
Using Publisher 2003 - Open up a blank page and try to insert picture from file or clip art. Empty picture box appears which can be resized, moved etc, but no graphic can be seen within box. Already reinstalled software. Insert functionality OK in other MS office programs IE word. View|Pictures|Detailed Pictures -- JoAnn Paules MVP Microsoft [Publisher] "Andrew" <Andrew@discussions.microsoft.com> wrote in message news:A5168099-4175-4FAF-9003-A5255F73F252@microsoft.com... > Using Publisher 2003 - Open up a blank page and try to insert picture > from > file...

Conditional Formatting based on Formula
The built-in rules for conditional formatting (greater than value, less than value, etc) work on the cell for which the CF is applied WITHOUT having to specify the cell. The examples for advanced rules using a formula require the formulas to respect their arguments. For instance, I'd like to have a CF that highlights numbers, so I'd rely on "ISNUMBER()" to get a true/false value that triggers the format. However, ISNUMBER() requires an argument, and I have been able to enter the cell's address as an argument to get it to work: apply CF to $A$1 by entering "=I...

Inserting Pictures #2
This is potentially a several pronged problem. I have several workbooks that have logos inserted on them. They are not set up as headers, just inserted. When I use the Send to Recipient option to e-mail the workbook via Outlook, the logos appear on the e-mail the person recieves, but when you print the e-mail out, they turn into big boxes with an X in the corner, like a picture on a web-site does when it won't load in your browser. Word and Access do this as well. I have tried inserting the picture and embedding it, as well as not embedding it. I've also tried opening the log...

selecting picture behind text
If you right click a picture and select the text wrapping to be "behind text", how do you later select the picture if you need to change something. I tried right clicking to change the text wrapping again and I kept selecting the text in front of it instead of the image. I got around it by doing and undo, but that is not a practical approach for future modifications etc. You can select pictures with the white 'Select' arrow in the Editing section of the Home tab With reference to your other query, see also http://www.gmayor.com/graphics_on_labels.htm and http://...

How do I save a Publisher document as my desktop picture?
I have created a simple Publisher document that is a reminder to "Log Out" when finished using computer as there are multiple users on our computer. How do I save this document so it shows as my desktop background picture? Thank you, Randy. Randy95 wrote: > I have created a simple Publisher document that is a > reminder to "Log Out" when finished using computer as > there are multiple users on our computer. How do I save > this document so it shows as my desktop background > picture? Thank you, Randy. =========================================== If you...

Picture uploads from Motorola V3Razr phone ??
Hi gang, can I upload pictures from my phone without phone tools please.. I lost the disc and the old pc died, sadly.. Thanx in advance, Paul Post Originated from http://www.VistaForums.com Vista Support Forums ...

Conditional Formatting
I want to color a cell based on the value of that cell being greater than zero and another cell not being equal to "x". I've used conditional formatting with "formula is" and formula - IF(A1>0,B1<>"X") This does not seem to work - what am I doing wrong?????? Use AND for multiple criteria to be satisfied, like this .. Assume you want to apply CF to col A Select col A, apply CF using Formula is: =AND(A1>0,B1<>"X") Format as desired>OK out Success? hit the YES below -- Max Singapore xde --- "Pat999"...

XL 2007 ...Conditional formatting ..
Hi, I would appreciate some help with some conditional formatting in XL2007. I have two colums of numbers A is the target number B is the real life number If A is 50 and B is 55, then B is 5 points off target. I would like to have a third colum showing the off target figure as a percentage of the target irrespective of the fact that the figure could be above or below the target. Like I don't care if the real life figure is more or less, if it's not on target (within a 5% margin of error) it needs to be corrected. Then I would like to set up the conditional formatting to show me ...

Conditional Formatting: > and < a date
I am trying to use this forumla as a Conditional Format =0<DAY(D17-NOW())<=10 It does not work. If I remove the 0< or <=10, it will work. Any suggestions? =AND(0<DAY(D17-NOW()),DAY(D17-NOW())<=10) "Brad P" <bradp_xx@hotmail.com> wrote in message news:ftd89m$q0u$1@news.datemas.de... >I am trying to use this forumla as a Conditional Format > > =0<DAY(D17-NOW())<=10 > > It does not work. If I remove the 0< or <=10, it will work. Any > suggestions? > Excellent, thanks for that! "GerryGerry" <Gerry@Ger...

Mail rules with multiple conditions in subject/body (OR vs AND)
Using Outlook 2007. I need a mail rule that runs if the subject contains "apples" AND "oranges" AND the body contains "bananas" (it's okay if bananas is also in the subject). But when creating rules, the conditions automatically become "OR" statements and there seems to be no way to change them to "AND" statements. Does anyone know a way around this? Thank you! You'll have to create additional rules. For an example see; http://www.msoutlook.info/question/220 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuri...

Sending several pictures as one whole imagen
Publisher 2007 Designing my Flyer I need to use several pictures . I want to send it as a WHOLE UNIQUE MESSAGE ...... NOT SHOWING MULTIPLE ATTATCHMENTS at the upper part of my Flyer. How can I avoid those references to the original pictures used to compose the flyer.? PDF is the preferred way. You could change your publication to a web page(HTML), but many folks do not accept HTML emails and the images will end up being attachments. There is an add-in for PDF on the Microsoft download web site. http://office.microsoft.com/en-us/downloads/CD101950461033.aspx -- Mary Sauer MSFT M...

OLE Object (picture) is not shown in publisher
Hello, I tryed to connect my publication with an access datasource (both 2007). I used in an access table the OLE datatype and put pictures in it. Sometimes as "linked", sometimes not. I tryed it with some file formats for pictures (tif, gif, bmp, jpg) - but the picture frames in Publisher 2007 are empty. I tryed to bring resolution and colors of the pictures down. This wont help. So - now I'm empty of idears. Please, can someone help me or give me a hint? When you try to insert a picture as a linked embedded object in an Office 2003 or 2007 Office program, the picture i...

publication 'loses' picture
Hi to everyone, I am creating a travel diary with PublisherXP, which is my first encounter with this program (so I am a complete novice to Publisher). I have no idea whether Publisher was made for such a use, but as it is, it works fine for me. So far I'm content with my publications (one for each day), but a few questions have arisen. This was the only newsgroup about Publisher I could find so I decided to ask here. I've googled quite a bit but was not successful. So much for the introduction, now one of my questions.:-) Sometimes (unfortunately I can't make out a regular patt...

How do I enter conditions
How do I enter conditions like: If A1 is <0.2 and A2 is >or = 0.2 then A3=Negative If A1 is >0.2 but <2.0 and A2 is Anything then A3 is IND. If A1 is > or = 2.0 and A2 is Anything then A3 is Positive. and get them to work? I would need all 3 conditions in a single cell. Is this possible? Look in HELP index for AND -- Don Guillett SalesAid Software donaldb@281.com "Brian" <brians34@grandecom.net> wrote in message news:575lb111vdvp3igtl6e5cpj0qupuamjcuq@4ax.com... > How do I enter conditions like: > > If A1...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Conditional pivot update
Hey guys :) I want to show all fields of my pivot-table - except the "Emty" (including each perpetual new revision). In the start of my macro the pivot-table is excluded the field "Emty" and this cause that new generated fields "Rev n+1)" are not shown in the "revised" pivot-table (unless I choose theese fields manually). How can I make my macro show all the fields, except the field "Emty", on very update? Very best regards Snoopy This is a outprint of the inadequate macro...... With ActiveSheet.PivotTables("Trend").PivotFields...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Conditional Formatting query
Hi all, I need to place conditions on a spreadsheet dealing with dates. In a column I have dates eg 28 Aug 07, 15 May 07, 20 Jun 08 etc Lets say they are in cells B3, B4 and B5 respectively. At cell A1 there is a date =TODAY() In the B column I have placed conditional formatting as Cell Value Is: Equal or greater than $A$1 - it returns a cell coloured RED I'm at a loss to place a value in for those dates between Cell A and those in Column B from day 1 to day 90 (ie within 3 months) to return a colour of Yellow Hope you can understand my query, Cheers, Pete. Select the range to be...

Conditional formatting in Charts?
Is it possible to add conditional formatting to charts? For example, if a is greater or equal to b the bar would be green and if a is less than b the bar would be red. I appreciate any help. Thank you! This should help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart "Todd" <anonymous@discussions.microsoft.com> wrote in message news:1e8601c4f7f7$f2f26090$a301280a@phx.gbl... > Is it possible to add conditional formatting to charts? > For example, if a is greater or equal to b the bar would > be green and if a is less than b the bar would be red....

insert picture from scanner doesnt work anymore
Every other Microsoft product works except Publisher. Have reinstalled printer and Publisher and contacted HP with not help Scan your picture and save it in a folder, insert into Publisher. Did you seek a newer driver while you were at HP? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dobr" <dobr@discussions.microsoft.com> wrote in message news:5D458581-7CEF-4235-B841-AB46C2ACBE37@microsoft.com... > Every other Microsoft product works except Publisher. Have reinstalled > printer and Publisher and contacted H...