PeterBunde
New Member
- Joined
- Dec 7, 2016
- Messages
- 45
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
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
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
Code:
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