y axis max value set fails

[PeterBunde]

Fellow sufferers!

I have a graph that is generated from an array by an event handler. I want the graph y axis max value to exceed the max value in the graph, by 1. That sometimes does not happen - the max value then lies 2 above the max value. I then tried (see code below) to adjust the max y axis value, but I get an error

Run-time error '-2147467259 (80004005)': Method 'Maximumscale' of object 'Axix' failed

.

Public Function popup_graph(urliurli As String)
Dim ser As Series

Dim a, b, c, d As Integer
a = 40
b = 35
c = 35
d = 25
Set url_defect_array = defects_of_category(urliurli)
max_value = WorksheetFunction.Max(Array(url_defect_array("ARed") + url_defect_array("ABlue") + url_defect_array("AAmber"), url_defect_array("BRed") + url_defect_array("BBlue") + url_defect_array("BAmber"), url_defect_array("CRed") + url_defect_array("CBlue") + url_defect_array("CAmber"), url_defect_array("DRed") + url_defect_array("DBlue") + url_defect_array("DAmber")))
      
    Set cht = Sheets("Approval (F3)").ChartObjects.Add(0, 0, 300, 300).Chart
    'cht.Activate
    With cht
        .ChartType = xlColumnStacked
        Set ser = .SeriesCollection.NewSeries
        With ser
           .Name = "Open"
           .XValues = Array("A", "B", "C", "D")
           .Values = Array(url_defect_array("ARed"), url_defect_array("BRed"), url_defect_array("CRed"), url_defect_array("DRed"))
           .Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End With
        Set ser = .SeriesCollection.NewSeries
        With ser
           .Name = "ASK"
           .XValues = Array("A", "B", "C", "D")
           .Values = Array(url_defect_array("ABlue"), url_defect_array("BBlue"), url_defect_array("CBlue"), url_defect_array("DBlue"))
           .Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
        End With
        Set ser = .SeriesCollection.NewSeries
        With ser
           .Name = "FIX"
           .XValues = Array("A", "B", "C", "D")
           .Values = Array(url_defect_array("AAmber"), url_defect_array("BAmber"), url_defect_array("CAmber"), url_defect_array("DAmber"))
           .Format.Fill.ForeColor.RGB = RGB(255, 215, 0)
        End With
        charto = .Name
        
        .Axes(xlValue).MajorUnit = 1
        
    End With
    Set shp = ActiveSheet.Shapes(cht.Parent.Name)
    SetChartPosition
      
    plotheight = a * max_value + b
    chartheight = c * max_value + d
    
    If max_value = 0 Then
    plotheight = 180
    chartheight = 80
    End If
    
      If max_value = 1 Then
     plotheight = 180
    chartheight = 80
    End If
    
          If max_value = 2 Then
     plotheight = 180
    chartheight = 80
    End If
    
          If max_value = 3 Then
     plotheight = 222
    chartheight = 100
    End If
        
    setplotareaheight
    setchartheight
    maximum_scale
    
End Function


Private Function maximum_scale()
Dim pLoop As Integer
cht.Parent.Activate
'cht.Activate
'cht.Select
With cht
' For pLoop = 1 To 5
       ' On Error Resume Next
.Axes(xlCategory).MaximumScale = max_value + 1
' Next
End With
End Function

What can I do to either force VBA to create the chart with a max y-axix value being 1 larger than the max value, or

 

[04nimmot]

Hi

I've had a little look and it appears that in your maximum_scale function xlCatergory should read xlValue, also max_value isn't being passed into the Function.

Below is code that I have working currently in my own chart, you would just need to feed it with your max_value + 1 instead of MaxScale.

Hope this helps

    Dim objChart As ChartObject
    Set objChart = ActiveSheet.ChartObjects(chtName)
    ActiveSheet.Unprotect
    With objChart.Chart
        With .Axes(xlValue)
            .MaximumScale = MaxScale
        End With
    End With