Chart Y Axis Scale Greater Than 100%

[WallyBackman]

Fantastic site!

I have multiple workbooks, each with multiple column charts in Excel 2003, showing a maximum of 120% even though none of the source figures are more than 100%. The source data for the chart comes from monthly percentages below the chart.

Please help with some code to look in each chart in the active workbook and if the Max Y Axis scale is greater than 1, change it to 1.

Thanks for your time.

 

[VoG]

Try

Sub xtest()
Dim ws As Worksheet, ch As ChartObject
For Each ws In ActiveWorkbook.Worksheets
    For Each ch In ws.ChartObjects
        ch.Chart.Axes(xlValue).MaximumScale = 1
    Next ch
Next ws
End Sub

 

[WallyBackman]

VoG,

Thanks for the quick reply.

The code worked to change the Max for all of the charts, but I only want to change the Max for those charts that exceed 100%.

 

[VoG]

Try

Sub xtest()
Dim ws As Worksheet, ch As ChartObject
For Each ws In ActiveWorkbook.Worksheets
    For Each ch In ws.ChartObjects
        If ch.Chart.Axes(xlValue).MaximumScale > 1 Then ch.Chart.Axes(xlValue).MaximumScale = 1
    Next ch
Next ws
End Sub

 

[WallyBackman]

Vog,

Works great!

Thanks for your time.