VBA for updating Chart Axis based on cell value

Topic

New Reply

Hi I need some code to update a chart axis but cant get it to work, i’m using:

Sub ChangeAxisScales()

Dash2.ChartObjects(“Chart 8”).Activate
With ActiveChart.Axes(xlCategory, xlPrimary)
.MaximumScale = [Axis_Max]
.MinimumScale = [Axis_Min]
End With
End Sub

The graph is on the worksheet Dash2 and the named cells for the scale are on Sheet8. I get a runtime error 440 -Method ‘MaximumScale’ of object ‘Axis’ failed.

Any ideas?

Reply | Quote

Replies

Idarley,

Try this:

Code:

Sub ChangeAxisScales()
Worksheets(“Dash2”).ChartObjects(“Chart 8”).Activate
With ActiveChart.Axes(xlValue)
.MaximumScale = WorksheetFunction.Max(Worksheets(“Sheet8”).Range(“Axis”))
.MinimumScale = WorksheetFunction.Min(Worksheets(“Sheet8”).Range(“Axis”))
End With
End Sub

Assuming that the range of chart data on Sheet8 has been named “Axis”.

HTH,
Maud

Reply | Quote

Thanks Maudibe, I could use the range of value but I want a bit of space between the high and low values which is why I have a formula in the named cells Axis_Min and Axis_Max. Is there any way to get it to run off those cell values?

Reply | Quote

This will use your naned ranges that already have your formulas applied:

Code:

Sub ChangeAxisScales()
Worksheets(“Dash2”).ChartObjects(“Chart 8”).Activate
With ActiveChart.Axes(xlValue)
.MaximumScale = Worksheets(“Sheet8”).[Axis_Max]
.MinimumScale = Worksheets(“Sheet8”).[Axis_Min]
End With
End Sub

Reply | Quote

Works perfect. Thanks!

Reply | Quote