How to generate on a chart or spreadsheet a date line marker

[u473]

How do I generate on a chart or worksheet, a date line marker which
would be a red arrow pointer object.
pointing to the 8th period out of 12 for example.
At this point, the Period # could be given in cell A1. [In the future
I would work directly with dates].
All I remember is that MOVE and TWIPS are involved.
To get some clues, I started the Macro Recorder as usual [From Excel
2007] to insert an arrow pointer,
make it red, position it, etc... but it did not record any of my
actions.
Your help will be appreciated,
J.P.

 

[Brenner]

Someone helped me with this a while back... The following code assumes that
your chart(s) is/are embedded within a worksheet. Also notice at the bottom
of the snippet the lines that start with "dropline.Line." - this is where you
change the formatting of your line... Hope it helps...

Regards,
John

***************************************************
Sub MoveLines()
Dim Wks As Worksheet
For Each Wks In Worksheets
If Wks.Name <> "Metrics" Then
For Each shp In Wks.Shapes
If shp.Type <> msoChart Then
shp.Delete
End If
Next shp
Set a = Wks.ChartObjects("Chart 1")
With Wks.ChartObjects("Chart 1")
LeftGraph = .Left
TopGraph = .Top
WidthGraph = .Width
HeightGraph = .Height
With .Chart
With .PlotArea
LeftChart = LeftGraph + .InsideLeft
TopChart = (TopGraph + .InsideTop) + 14
WidthChart = .InsideWidth
HeightChart = .InsideHeight
End With
Set b = .Axes
With .Axes(xlCategory)
MinDate = .MinimumScale
MaxDate = .MaximumScale
End With
End With
XPos = (((Now() - (MinDate + 1)) / (MaxDate - MinDate) *
WidthChart) + _ LeftChart) - 1
Set dropline = Wks.Shapes.AddLine(XPos, (TopChart * 2.5), XPos, _
TopChart + HeightChart)
dropline.Line.DashStyle = msoLineDash
dropline.Line.ForeColor.RGB = RGB(248, 0, 0)
dropline.Line.Weight = 2
End With
End If
Next
End Sub
***************************************************

 

[u473]

Thanks a lot. This is putting me in the right direction.
JP.