Changing Chart Axes using a macro

[AndrewCrisp]

I have written a macro to use values calculated on a worksheet to use as min and max for an axis. The chart is embedded on the same work sheet.

Code used is as follows:

Sub planreportaxis()

Sheets("Report 1").Charts("chart 1").Select

Dim maxaxe As Range
Dim minaxe As Range

Set maxaxe = Range("ah6")
Set minaxe = Range("ah7")

With Ch.Axes
MinimumScale = minaxe
MaximumScale = maxaxe
End With


End Sub

I can't seem to get past the first line i.e. it does not work. Please can anyone help. Also when written can the macro me made to invoke automatically when the data changes?

Many thanks

Andrew

[T-J]

You need to use the ChartObject object instead of Chart object, as follows:

Sub planreportaxis2()
Dim maxaxe As Variant
Dim minaxe As Variant

Sheets("report 1").ChartObjects("chart 1").Select
minaxe = Range("ah7").Value
maxaxe = Range("ah6").Value

With ActiveChart.Axes(xlValue)
.MinimumScale = minaxe
.MaximumScale = maxaxe
End With

End Sub

For the second part, have a look at the Worksheet Change event.