How do I scale excel chart axes from vb?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Thanks,
Scott
 
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.
 
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
 
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
 
Back
Top