possible to CHANGE Y AXIS ON CHARTS WITH VBA?

[erickguz]

Hello,

I tried some vba examples but couldn't quite get the following to work.

I have about 50 charts per sheet on a worksheet.

Is it possible to change the y axis for the 50 sheets on a sheet with vba, without affecting the other sheets?

Right now for example, I have

MIN: 0
MAX: 50
MAJOR UNIT: 25

need to change to:

MIN: 2000
MAX: 5000 (or auto)
MAJOR UNIT: 500

Thank you.

 

[RoryA]

Yes it is possible. What code did you try, and what happened?

It's basically a loop like this:

dim oCht as chartobject
for each ocht in sheets("sheet name").chartobjects
With ocht.chart.axes(xlValue, xlPrimary)
      .MaximumScale = 5000
      .MinimumScale = 2000
      .MajorUnit = 500
    End With
next ocht

 

[erickguz]

Thanks Rory, This worked very well for the charts that I needed changed. I had a code that i got off a random site but it was about a page long... It just made everything zero - not sure honestly, I'm not new to excel, just not familiar with vba

Is it possible to adjust this code and create separate macros?

1. have a macro that works only on selected charts

2. Make it AUTO maximum and minimum with Major Unit of 500

 

[RoryA]

For 1, remove the loop and use

With activechart.axes(xlValue, xlPrimary)
      .MaximumScale = 5000
      .MinimumScale = 2000
      .MajorUnit = 500
    End With

for 2, you can use:

With ocht.chart.axes(xlValue, xlPrimary)
      .MaximumScaleIsAuto = True
      .MinimumScaleIsAuto = True
      .MajorUnit = 500
    End With