Macro to graph (or draw) a vertical line on the chart

[Brenner]

Each of my charts has about 60 days along the Y axis, and I would like the
senior manager to be able to see a dotted line that goes from the Y axis
(always from today's date) to the data point that represents today's value.

Is it possible to add a vertical series? Or to graph or draw a vertical line
that will "walk" along the Y axis?

Thanks!
John

 

[Andy Pope]

Hi,

Like this?
http://www.andypope.info/ngs/ng25.htm

Cheers
Andy

 

[Brenner]

Yes!

I'll try it!

Thanks,
John
--
Cool

Hi,

Like this?
http://www.andypope.info/ngs/ng25.htm

Cheers
Andy

 

[Joel]

This will add a line with a macro

Sub createline()

For Each cht In ActiveSheet.ChartObjects
' MsgBox cht.Name

Next cht

Set a = ActiveSheet.ChartObjects("Chart 1")

With ActiveSheet.ChartObjects("Chart 1")
LeftGraph = .Left
TopGraph = .Top
WidthGraph = .Width
HeightGraph = .Height

With .Chart
With .PlotArea

LeftChart = LeftGraph + .InsideLeft
TopChart = TopGraph + .InsideTop
WidthChart = .InsideWidth
HeightChart = .InsideHeight
End With
Set b = .Axes
With .Axes(xlCategory)
MinDate = .MinimumScale
MaxDate = .MaximumScale
End With
End With
'scale todays date on the x-axes to get postiion
XPos = ((Now() - MinDate) / (MaxDate - MinDate) * _
WidthChart) + LeftChart

Set dropline = ActiveSheet.Shapes.AddLine( _
XPos, TopChart, XPos, TopChart + HeightChart)
dropline.Line.DashStyle = msoLineRoundDot
dropline.Line.ForeColor.RGB = RGB(50, 0, 128)
dropline.Line.Weight = 3

.SendToBack
End With

End Sub

 

[Brenner]

--
Cool

This will add a line with a macro

Sub createline()

For Each cht In ActiveSheet.ChartObjects
' MsgBox cht.Name

Next cht

Set a = ActiveSheet.ChartObjects("Chart 1")

With ActiveSheet.ChartObjects("Chart 1")
LeftGraph = .Left
TopGraph = .Top
WidthGraph = .Width
HeightGraph = .Height

With .Chart
With .PlotArea

LeftChart = LeftGraph + .InsideLeft
TopChart = TopGraph + .InsideTop
WidthChart = .InsideWidth
HeightChart = .InsideHeight
End With
Set b = .Axes
With .Axes(xlCategory)
MinDate = .MinimumScale
MaxDate = .MaximumScale
End With
End With
'scale todays date on the x-axes to get postiion
XPos = ((Now() - MinDate) / (MaxDate - MinDate) * _
WidthChart) + LeftChart

Set dropline = ActiveSheet.Shapes.AddLine( _
XPos, TopChart, XPos, TopChart + HeightChart)
dropline.Line.DashStyle = msoLineRoundDot
dropline.Line.ForeColor.RGB = RGB(50, 0, 128)
dropline.Line.Weight = 3

.SendToBack
End With

End Sub

 

[Jerry]

Hi!

I was looking for the exact macro (I run it in a loop to highlight several
event dates).
However, when I apply the code, it is always slightly off:
1. Vertical bar but starts slightly above the plot area
2. It is also slightly off in terms of dates (about two days)
This seems weird as everything in the code is automatic (.Top, etc.)
I checked the axis and they are 100% standard, y cutting x at zero, etc.

Am I missing something obvious?
Thank you in advance for your help.

 

[John Brenner]

Hi Joel -

This did the trick for my last project, but now I have a textbox that has
some dynamic text that should also move with the 'date' line.

Is there some code that I can insert within your piece of code that will get
'Text Box 1027' to change position when the line moves?

Thanks!

 

[John Brenner]

Hi Jerry -

I didn't see your post earlier - so this may be far too late to help you...
But if not -

I also noticed that the lines are slightly off - and after having worked
with it, I noticed that the vertical line marches along the x-axis as time
goes by during the day. If my lines move day to day, then depending on the
time of day, the line will move between the tick marks. Early AM has it
closer to the tick mark on the left, and later in the afternoon/evening has
the vertical line closer to the right tick mark.

Please let me know if you still have a problem, and I'll see what I can do...