How do I scale excel chart axes from vb?

[scmancl21]

Is there a way to change the axis scale of an existing chart in an excel
spreadsheet from vb code?

Thanks,
Scott

 

[bj]

If you can change it while you look at it you can change it with a macro.

What Kind of chart is it?
is it the X, Y or Z axis you wish to change?

I would first try to record a macro of me trying to change it the way You
want it changed and then go from there.

 

[John Mansfield]

Yes, you can. Please see Jon's explanation:

http://www.peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Here are some more examples:

'(1) Embedded chart - sets primary axis based on hardcoded inputs:

Sub SetAxes1()
Dim Cht As Chart
Set Cht = Sheets("Sheet1").ChartObjects("Cht01").Chart
With Cht.Axes(xlValue)
.MaximumScale = 6
.MinimumScale = 1
.MajorUnit = 0.5
End With
End Sub

'(2) Embedded chart - sets primary axis based on worksheet cell inputs:

Sub SetAxes2()
Dim Cht As Chart
Set Cht = Sheets("Sheet1").ChartObjects("Cht01").Chart
With Cht.Axes(xlValue)
.MaximumScale = Range("A1")
.MinimumScale = Range("A2")
.MajorUnit = Range("A3")
End With
End Sub

'(3) Embedded chart - sets primary and secondary axis based on worksheet
cell inputs

Sub SetAxes3()
Dim Cht As Chart
Set Cht = Sheets("Sheet1").ChartObjects("Cht01").Chart
With Cht.Axes(xlValue, xlPrimary)
.MaximumScale = Range("A1")
.MinimumScale = Range("A2")
.MajorUnit = Range("A3")
End With
With Cht.Axes(xlValue, xlSecondary)
.MaximumScale = Range("B1")
.MinimumScale = Range("B2")
.MajorUnit = Range("B3")
End With
End Sub

'(4) Chart sheet set axis

Sub SetAxes4()
Dim Cht As Chart
Set Cht = Sheets("Chart1")
With Cht.Axes(xlValue)
.MaximumScale = Range("A1")
.MinimumScale = Range("A2")
.MajorUnit = Range("A3")
End With
End Sub

 

[Tushar Mehta]

From inside of XL turn on the macro recorder (Tools | Macro > Record
new macro...) do whatever you want using the UI, and turn off the
recorder. XL will give you the necessary code which you will have to
tailor to your specific needs.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions