Hi All, I am trying to add a vertical line (via macro) to my graph and can't quite grasp it.
This code adds the vertical line but changes the whole graph to scatter (which mucks up my x-axis)
This code should do the trick (only change the new series to scatter)
But it completely destroys the axis
Out of interesting, a naive recording works and leads me to believe that I am defining the Xvalues wrong (even though it works in the first case)
Thanks!
This code adds the vertical line but changes the whole graph to scatter (which mucks up my x-axis)
Code:
Sub VertLine2()
Dim ChartName As String
Dim Labels As String
Dim vntArray As Variant
Dim maxinput As Long
dateinput = 37803
maxinput = ActiveChart.Axes(xlValue).MaximumScale
ActiveChart.Axes(xlValue).MaximumScale = maxinput
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
With ActiveChart.SeriesCollection.NewSeries
.Name = "Line"
.XValues = Array(dateinput, dateinput)
.Values = Array(0, maxinput)
.Format.Line.Visible = msoTrue
.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
.MarkerStyle = -4142
End With
End Sub
This code should do the trick (only change the new series to scatter)
Code:
Sub VertLine()
'Dim arLabels As String
'Dim exLabels As String
'Dim lablerange As Range
'Dim yarray() As Variant
Dim ChartName As String
Dim maxinput As Long
Dim dateinput As Long
'Fix the y-axis so that line cuts through
maxinput = ActiveChart.Axes(xlValue).MaximumScale
ActiveChart.Axes(xlValue).MaximumScale = maxinput
' Enter date at which to add line
dateinput = 37803
'''Get x-values'''
'arLabels = ActiveChart.SeriesCollection(1).Formula
'exLabels = Mid(Mid(arLabels, InStr(arLabels, ",") + 1), 1, InStr(1, Mid(arLabels, InStr(arLabels, ",") + 2), ","))
'Debug.Print exLabels
'Set LabelRange = Range(exLabels)
''Create corresponding array
'counter = 1
'For Each cell In LabelRange
' ReDim Preserve yarray(1 To counter)
' If cell = dateinput Then
' yarray(counter) = maxinput
' End If
' counter = counter + 1
'Next cell
'''Get x-values'''
'Insert line
ActiveChart.SeriesCollection.NewSeries.Name = "VertLine"
'Change to scatter
With ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
.ChartType = xlXYScatterLinesNoMarkers
.XValues = Array(dateinput)
.Values = "={0,1000}"
.Format.Line.Visible = msoTrue
.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
.MarkerStyle = -4142
End With
'Delete legend
ActiveChart.Legend.LegendEntries(ActiveChart.Legend.LegendEntries.Count).Delete
End Sub
But it completely destroys the axis
Out of interesting, a naive recording works and leads me to believe that I am defining the Xvalues wrong (even though it works in the first case)
Code:
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""Line"""
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(4).Select
ActiveChart.SeriesCollection(4).ChartType = xlXYScatterLinesNoMarkers
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).XValues = "='C Data'!$A$84,'C Data'!$A$85"
ActiveChart.SeriesCollection(4).Values = "={0,1000}"
Thanks!