CODEWITHSUNDEEP
vbaexcel
Meesha
Meesha

Reputation: 821

Vertical line on a Bar Graph

enter image description here

How do I add a vertical line at 25 points. I tried using add series method but somehow I am not able to get the range that I need to give for the line. I get a horizontal line at 25 but I need a vertical one.

  ActiveChart.SeriesCollection.NewSeries
  ActiveChart.SeriesCollection(4).ChartType = xlLine
  ActiveChart.SeriesCollection(4).XValues = Sheets("Sheet1").Range("B5")

B5 cell has value 25. Also I figured out that I get a horizontal line at 25 if I use only Values instead of Xvalues in my code, so I am pretty sure I need to use Xvalues but not able to get the range to get my job done. In the current code the line is nowhere to be seen but legend shows that a new series was added.

Addition: Also if I use XlcolumnStacked chart type then the horizontal line works perfectly for me. (When I use values I get a horizontal line, which is perfect for column chart instead of bar).

Upvotes: 0

Views: 1368

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

This should do it:

Public Sub AddVerticalLine()
    Dim k&, s&
    k = Sheet1.[b5]
    With ActiveChart
        s = .SeriesCollection.Count
        With .SeriesCollection.NewSeries
            .ChartType = xlXYScatterLines
            .MarkerStyle = xlNone
            .Formula = Replace("=SERIES(,{.;.},{1;0}," & s & ")", ".", k)
        End With
        With .Axes(xlValue, xlSecondary)
            .MaximumScale = 1
            .Delete
        End With
    End With
End Sub

To accomplish adding the vertical line, you need to add that line's series as a Chart Type of XYScatterLines. That chart type defaults to including marker dots for each data point on the line. I assumed you did not want those because they are visually distracting. I set them to xlNone.

When working with XYScatter type charts you need to provide two numbers for each data point... an x-value and a y-value. To draw a line, you'll need two data points. That's a total of FOUR numbers that we need to include in the series to be plotted. Based on your example it appears you wish to key the line from cell B5's value. So I worked with that and created the four numbers for the line based on cell B5's value.

The next issue is to realize that this new line will scale based on the secondary y-axis. Once we add the vertical line, Excel makes this additional y-axis visible, but it assumes that we want some padding and so picks a maximum value range of the scale a little higher than our line. My code resets the scale so that the line fills the entire vertical space of the plot area.

Finally, I deleted this secondary axis, which does not actually delete it, but rather hides it.

That's it.

Upvotes: 1

Related Questions