none
Graph problem with Axis Labels RRS feed

  • Question

  • Hi,
    I'm trying to make a Graph using macros in excel (2013),  this graph need horizontal lines in order to meet the requirements of my boss (target values or limits value).

    The Graph is quite simple, a list of months (A column) and percent values (B column)

    I need to work this with XY or Line Graph Type, but

    When I work with the Graphic Type: xlXYScatterLines the limit lines are drawn properly, but I lose the Month Labels in the X Axis
    When I work with the Graphic Type: xlLineMarkers the Labels are correct, but the target lines are drawn only in the first X value.

    I already try different methods to make the horizontal lines, but this one has been the most simple and the only one that really works.
    I'm attaching some images (sorry, the web tells me that I can't attach any image or link until they verified my account ??¡¡??¡¡) 

    Some Code:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("A1:B13")
    (After inserting my main CHART, I make the limit horizontal lines with arrays)

    'First Line
        With ActiveChart
            Set s = .SeriesCollection.NewSeries()
            With s
                .Name = ""
                .Values = Array(0.25, 0.25)  'Position (Y) of the first line
                .XValues = Array(0, 14)        'X Points for the Horizontal Line
                .Border.Color = vbRed
            End With
        End With

         'Bottom line
         With ActiveChart
            Set s = .SeriesCollection.NewSeries()
            With s
                .Name = ""
                .Values = Array(0.01, 0.01) 'Position (Y) of the second line
                .XValues = Array(0, 14)       'X Points for the Horizontal Line
                .Border.Color = vbGreen
            End With
        End With

        'ActiveChart.ChartType = xlXYScatterLines  'When use this type of chart, the months labels are lost
        ActiveChart.ChartType = xlLineMarkers       'When use this type of chart, the horizontal line are broke

    Thanks in advance and forgive my English. Any help is welcome! 



    • Edited by kardomotta Wednesday, April 5, 2017 2:46 PM
    Wednesday, April 5, 2017 2:45 PM

Answers

  • Hi kardomotta,

    from the description of the thread , I can see that you want to add month names as Axis Label.

    you need to set SeriesCollection().XValues of chart.

    like below.

    ActiveChart.SeriesCollection(1).XValues = "=Request!$A$1:$A$6"
     

    Reference:

    Series.XValues Property (Excel)

    your modified code:

    Sub demo()
    
    Dim s As Object
    ActiveSheet.Shapes.AddChart.Select
     ActiveChart.SetSourceData Source:=Range("A1:B13")
     '(After inserting my main CHART, I make the limit horizontal lines with arrays)
    
    'First Line
         With ActiveChart
             Set s = .SeriesCollection.NewSeries()
             With s
                 .Name = ""
                 .Values = Array(0.25, 0.25)  'Position (Y) of the first line
                 .XValues = Array(0, 14)        'X Points for the Horizontal Line
                 .Border.Color = vbRed
             End With
         End With
    
          'Bottom line
          With ActiveChart
             Set s = .SeriesCollection.NewSeries()
             With s
                 .Name = ""
                 .Values = Array(0.01, 0.01) 'Position (Y) of the second line
                 .XValues = Array(0, 14)       'X Points for the Horizontal Line
                 .Border.Color = vbGreen
             End With
         End With
    ActiveChart.SeriesCollection(1).XValues = "=Request!$A$1:$A$6"
        'ActiveChart.ChartType = xlXYScatterLines  'When use this type of chart, the months labels are lost
         ActiveChart.ChartType = xlLineMarkers       'When use this type of chart, the horizontal line are broke
    
    
    End Sub
    

    Output:

    you can modify that line as per your requirement.

    Regards

    Deepak


    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 MSDNFSF@microsoft.com.

    • Marked as answer by kardomotta Thursday, April 6, 2017 5:31 PM
    Thursday, April 6, 2017 2:59 AM
    Moderator

All replies

  • Hi kardomotta,

    from the description of the thread , I can see that you want to add month names as Axis Label.

    you need to set SeriesCollection().XValues of chart.

    like below.

    ActiveChart.SeriesCollection(1).XValues = "=Request!$A$1:$A$6"
     

    Reference:

    Series.XValues Property (Excel)

    your modified code:

    Sub demo()
    
    Dim s As Object
    ActiveSheet.Shapes.AddChart.Select
     ActiveChart.SetSourceData Source:=Range("A1:B13")
     '(After inserting my main CHART, I make the limit horizontal lines with arrays)
    
    'First Line
         With ActiveChart
             Set s = .SeriesCollection.NewSeries()
             With s
                 .Name = ""
                 .Values = Array(0.25, 0.25)  'Position (Y) of the first line
                 .XValues = Array(0, 14)        'X Points for the Horizontal Line
                 .Border.Color = vbRed
             End With
         End With
    
          'Bottom line
          With ActiveChart
             Set s = .SeriesCollection.NewSeries()
             With s
                 .Name = ""
                 .Values = Array(0.01, 0.01) 'Position (Y) of the second line
                 .XValues = Array(0, 14)       'X Points for the Horizontal Line
                 .Border.Color = vbGreen
             End With
         End With
    ActiveChart.SeriesCollection(1).XValues = "=Request!$A$1:$A$6"
        'ActiveChart.ChartType = xlXYScatterLines  'When use this type of chart, the months labels are lost
         ActiveChart.ChartType = xlLineMarkers       'When use this type of chart, the horizontal line are broke
    
    
    End Sub
    

    Output:

    you can modify that line as per your requirement.

    Regards

    Deepak


    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 MSDNFSF@microsoft.com.

    • Marked as answer by kardomotta Thursday, April 6, 2017 5:31 PM
    Thursday, April 6, 2017 2:59 AM
    Moderator
  • Dear Deepak!

    Thank's a lot for all your time and your reply. Just when I was going to give me up!



    Thursday, April 6, 2017 5:29 PM