Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Min and max values on xy graph axis (OXP)

    Hi

    Is it possible to link the minimum and maximum values on the axis of a xy graph to cells in a spredsheet?

    Thank You

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min and max values on xy graph axis (OXP)

    Say that you have only one chart on a worksheet. The minimum and maximum values for the y (value) axis are in B1 and B2, respectively (they can be values entered by the user, or the result of formulas). The following macro will adjust the y axis:

    Sub AdjustAxis()
    With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
    .MinimumScale = Range("B1")
    .MaximumScale = Range("B2")
    End With
    End Sub

    If you want the adjustment to be automatic, you would have to call this macro from the Worksheet_Change and/or Worksheet_Calculate events.

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min and max values on xy graph axis (OXP)

    Thank You Hans
    My graph is chart sheet and I have already put the following lines in the chart activate event:
    Chart17.Axes(xlCategory).MinimumScale = Sheet15.Range("M3").Value
    Chart17.Axes(xlCategory).MaximumScale = Sheet15.Range("M27").Value
    I was actualy hoping to do it without a macro.

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Min and max values on xy graph axis (OXP)

    As far as I know, you cannot link the minimum and maximum properties directly to cell values; the Format Axis dialog does not accept cell references or formulas in the Minimum Scale and Maximum Scale properties.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Min and max values on xy graph axis (OXP)

    The "closest" I have come to setting the min/max with formulas (and it is not completely satisfactory), is actually to modify the category range to eliminate points outside the desire range (keep a range of the actual, but plot another column/row using an IF) by replacing the value with an NA. If you want to expand outside the range of the data, just add a min/ max x -point to the data set and it will expand to include those.

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Min and max values on xy graph axis (OXP)

    Hi Frits, its great to hear from another South African....this species is very rare on this site!! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I would consider the setup you have as linked to cells anyway! All you need to do is activate the chart sheet and the macro takes care of updating the x/y axis values automatically! This is in my opinion a better setup anyway! (Maybe you are just enquiring??)

    Anyhow...have a great day further!
    Regards,
    Rudi

Posting Permissions

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