Hi,

I have been asked to modify some Excel charts to override the autoscaled axis that Excel uses. The request is that the axis should be larger than largest value that it is graphing. For example if I am graphing a series of data points from 1 to 8 then the scale needs to go up to 9 or 10 and also on the other end of the scale it would need to show -1 or -2 depending on what the user wants to show.

I tried writing some VBA code to do this and it appears to work except once the macro is linked to the chart I am unable to modify the chart. By that I mean I can no longer select the columns or axes or do any modification.
The macro just links to two cells in the spreadsheet that specify the max and min values. This is how I would like to create the chart so that the user can changes those cells if he wishes to increase or decrease the scale.

Sub ChangeAxes()
With ActiveSheet.ChartObjects("Chart 2").Chart

    With .Axes(xlValue)
        .MaximumScale = ActiveSheet.Range("$O$6").Value
    ' Constant value
        .MinimumScale = ActiveSheet.Range("$O$7").Value
    ' Constant value
    '.MajorUnit = ActiveSheet.Range("A1").Value
    ' Worksheet range value End With
    End With
End With
End Sub
Is there a way of defining the max and min values of the axes without running into this problem? Simply going into the Format Axis box won't do as the user is not comfortable making these changes and wants it all done automatically.

I appreciate any help you can give me on this.

Thanks