Set Excel Chart so maximum scale of y-axis is at least 100%

[bisel]

Greetings,

I have a bar graph with the y-axes expressed in percent. The minimum percent value is always 0%. However, the maximum percent value can exceed 100%. Sometimes, depending on the data, if I set the y-axes maximum scale for Auto, the chart may have a scale that is less than 100%. I want to make sure that the y-axes is at least 0% to 100% ... more than 100% is OK.

I am trying this VBA without success. I get a run-time error without specifics. Here is the code ....

    ChartObjects("percentfunding_chart").Activate
    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = False
    If ActiveChart.Axes(x1Value).MaximumScale < 1 Then
        ActiveChart.Axes(x1Value).MaximumScale = 1
        Else
    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True
    End If

Appreciate any help.

Thanks,

Steve

 

[bisel]

I may have solved my own problem. I added the line to select the axes ...

    ChartObjects("percentfunding_chart").Activate
    
    ActiveChart.Axes(xlValue).Select

    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True ' first set to true to allow autoset
    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = False ' then set to false to get the value of maxscale
    testx = ActiveChart.Axes(xlValue).MaximumScale ' test the maxscale
    If textx < 1 Then
        ActiveChart.Axes(xlValue).MaximumScale = 1 ' set max scale to 1 if less than 1
    Else
    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True ' set maxscale back to auto if 1 or more
    End If

 

[bisel]

Spoke too soon. This code seems to be working.

    ChartObjects("percentfunding_chart").Activate
    
    If Sheet2.Range("maxpctfunding") < 1 Then
        ActiveChart.Axes(xlValue).MaximumScale = 1 ' set max scale to 1 if less than 1
    Else
        ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True ' set maxscale back to auto if 1 or more
    End If