Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Commands (Excel 2000)

    I am trying to use the IF command to insert a picture if the statement is true. I can not do it. Is their a trick to doing this using another command.

    The if statement I used was IF(j21>0,Picture,0). But I can not get it to work. Help

    Thanx

    Jerry M

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Commands (Excel 2000)

    I think that the only way to accomplish this would be to insret VBA code into the worksheet change event routine to insert the picture if J21>0 or remove it if it is not.

    That code would look something like this:

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("J21")) Is Nothing Then
    If ActiveSheet.Range("J21").Value > 0 Then
    ActiveSheet.Pictures.Insert ("Cocuments and SettingsLegare ColemanMy DocumentsMy PicturesSAMPLE.JPG")
    End If
    End If
    End Sub
    </pre>

    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CAMERA BUTTON ALERT!

    I think what you want is the undocumented - in 97 and it might as well be in 2000 - camera button which enables you to use =if(,,) to selectively display charts or pictures.

    rightclick on a blank toolbar area and select customize - it's down the bottom. You should get the customise dialog. Select the commands tab and then select the tools category. Scroll down the commands til you find camera. drag this to a toolbar and close the customise dialog.

    now click on the button and drop your picture where you want it. You should find that in the formula box you have a formula along the lines of "=$H$29". Edit this to say "=selectchart" and hit enter.

    (I have heard/read that you can just insert a picture object and then edit the formula to get to the same point but have never tried doing it this way.)

    now for the fun bit. you need a switch - this can be a cell or a control on your sheet but we'll stick with cell a1. you also need two graphs (more are possible but two will do to demonstrate.) Mine cover the ranges l22:l29 and l32:l39 for this example. So now you need to go to Insert:Nameefine. In the "names in workbook" box enter the word selectchart. in the "refers to" box enter the following formula without the quotes "=IF(Sheet1!$A$1>5,Sheet1!$L$22:$Q$29,Sheet1!$L$32 :$Q$39)"

    now play with the value of a1 and see if that gets you anywhere near where you want to go - but make sure you've got your charts in the right ranges.

    FWIW, I've never managed to display cell contents this way, just pictures that rest over the referenced range.

    also FWIW, you have to use defined names - it just won't work if you edit the picture box formula directly.

    Any good?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CAMERA BUTTON ALERT!

    Hey, I didn't know that one! Cool.

    <Off playing with this new tool> <bg>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CAMERA BUTTON ALERT!

    Good solution!
    Legare Coleman

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CAMERA BUTTON ALERT!

    There is another way to get the Camera Tool effect without actually using the camera. Select a range, and holding Shift select Edit, and a new option Copy Picture is available. Select this and take the As Shown on Screen option. Now holding Shift, select Edit and again a new option to Paste Picture. Paste the picture, select it and in the formula bar enter = range, eg A1:J100, and the picture should expand to accomadate any data that is in that range. (any existing contents will be lost) . It is stored as a picture and so can be resized without effecting anything else on the sheet.

    You can use the Paste Picture command to paste any contents of the Clipboard to the sheet as either a picture or text box as appropriate.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •