dynamic charts and axes scaling

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.

Private Sub Worksheet_Change(ByVal Target As Range)


    If Not Intersect(Target, Range("C1")) Is Nothing Then
        If ActiveSheet.ChartObjects.Count > 0 Then
            ActiveSheet.ChartObjects(1).Chart.Axes(xlValue).MinimumScale = Range("C4").Value
        End If
    End If
End Sub

Re: dynamic charts and axes scaling

The other axis is xlCategory, which in the case of an xy scatter is the x axis.

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.

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?

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