Draw lines in otherwise bar chart? RRS feed

  • Question

  • Fellow VBA sufferers!

    I currently am generating a graph of open defects og for different categories (A-D). Now, there is a threshold for each category (being zero for category A and B).

    Please propose a way I can draw these thresholds as a line in the chart? See attaches illustration (which I did in Powerpoint). My current code is:

        Public Function popup_graph(urliurli As String)
        Dim cht As Chart
            Set url_defect_array = defects_of_category(urliurli)
            Dim ser As Series
            Set cht = Sheets("New Report").ChartObjects.Add(0, 0, 300, 300).Chart
            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
            End With
            Set Shp = ActiveSheet.Shapes(cht.Parent.Name)
        End Function
        Public Sub SetChartPosition()
            Dim intLeft As Integer
            Dim intTop As Integer
            intLeft = Application.Selection.Offset(0, 10).Left
            'intLeft = Range("e2").Offset(0, 10).Left
            intTop = Application.Selection.Offset(0, 30).Top
            'intTop = Range("e2").Offset(0, 30).Top
            Shp.Left = intLeft
            Shp.Top = intTop
            Shp.IncrementTop 22
        End Sub

    BW Peter B Hansen (extracting data via REST API/JSON from different agile dev tools and test tools tools and displaying it in Excel to the awe of my colleagues)

    Tuesday, July 11, 2017 12:22 PM

All replies

  • Hello,

    Do you try to draw the line in Excel manually by adding NewSeries or some other methods instead of inserting line shapes in the chart?

    I think the only way to get your want is to insert line shapes in the chart. However, if the series value are changed, the axis would be changed but the line shapes are fixed, so the solution makes no sense.

    We are unable to get the point I marked in the following picture. Excel Object Model doesn't provide any methods or properties for that. So in my opinion, we could not get your expected result programmatically.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Tuesday, July 18, 2017 6:44 AM