dynamic charts and axes scaling

  • I have attached a spread sheet and chart combo that almost achieves what I am after. The dynamic range works and the chart updates fine, but the scale on the axes cannot be set using formula (as far as I can tell) so how do I get the scales to either change according to a value I set on the sheet or auto scale so that if I select a range from say 60 to 100 (using the validation lists at C1 and C2) then the scale is something around 50 to 110 instead of 0 to 110 or whatever auto scale comes up with?


    I have had a look through previous posts but have found nothing to show this apart from setting scales to auto which does not achieve what I am after(I dont think).

    Files

    • Book1.xls

      (16.38 kB, downloaded 137 times, last: )
  • Re: dynamic charts and axes scaling


    Hi,


    Yes there is no way to automatically link scale max, min, major or minor value to cells.
    You can use the worksheet change event to update the chart though.
    Right click the sheet tab and select View Code. Add the following to the code module.

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Not Intersect(Target, Range("C1")) Is Nothing Then
    3. If ActiveSheet.ChartObjects.Count > 0 Then
    4. ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MinimumScale = Range("C4").Value
    5. End If
    6. End If
    7. End Sub

    [h4]Cheers
    Andy
    [/h4]

  • Re: dynamic charts and axes scaling


    Thankyou Andy, that looks great. I have not tried it yet and will have a play... I assume you can set maximumscale = range etc as well but how do you distinguish between x and y? there is xlvalue but that is not related to x axis (is it?)...


    Thanks again


    Justin

  • Re: dynamic charts and axes scaling


    This question is related to Axis scaling for scatter charts. I enter variable X& Y values into a work sheet The chart is set up to auto scale the max and minimum axis values (ie scale is based on the input values for any particular set of data) Because of this the minimum and maximum values of the two axes are invariably different but I would like them always to be the same. I can manually set the scales the same for each time I plot data by entering chart options, but I would like to do this automatically. Do I need to write a macro to do this and if so, how would I go about doing it

  • Re: dynamic charts and axes scaling


    Hi,


    Yes you need a macro to set the Axis scale values if you want to override the automatic ones. Easiest way is to use the macro recorder to generate the code.


    You can then assign the macro to a button so it updates when you click or you can use a worksheet event to trigger the macro.


    Try the record macro and search the forum for worksheet event code. If you are still stuck post back and include an example of the chart.

    [h4]Cheers
    Andy
    [/h4]

  • Re: dynamic charts and axes scaling


    Many thanks for your help Andy
    I need to read up a little on workseet events to trigger an action but for now I have recorded the macro and have assigned it to a button.
    This achieves the result I requested and it Works just great!!


    PeteL

  • Re: dynamic charts and axes scaling


    So Andy I was happy yesterday and today I want to do more.
    There are two problems with the Macro to Automatically set the X & Y Axis scale for a chart based on input data to a worksheet
    1) Protecting the worksheet with a password then requires a password before the macro cna be operated
    2)The Chart scale does not change with each input of new data but only when the macro is operated. (Ie if the next data point is off range from the previous scale it won't be visible in the plot until the macro is operated)


    Would a "worksheet event" that is activated by a change in max/min values in cells B1 & B2 solve these issues and if so, what would be the code?

    Files

    • Autoscale.xls

      (32.26 kB, downloaded 112 times, last: )
  • Re: dynamic charts and axes scaling


    No need to respond, I've answered my own questions and have created a simple program, just a few lines of code that works fine
    PeteL