Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
Hi,
Like this? http://www.andypope.info/ngs/ng25.htm Cheers Andy Brenner wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
Yes!
I'll try it! Thanks, John -- Cool "Andy Pope" wrote: Hi, Like this? http://www.andypope.info/ngs/ng25.htm Cheers Andy Brenner wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
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 "Andy Pope" wrote: Hi, Like this? http://www.andypope.info/ngs/ng25.htm Cheers Andy Brenner wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
-- Cool "Joel" wrote: 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 "Andy Pope" wrote: Hi, Like this? http://www.andypope.info/ngs/ng25.htm Cheers Andy Brenner wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
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! -- Regards, John "Joel" wrote: 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 "Andy Pope" wrote: Hi, Like this? http://www.andypope.info/ngs/ng25.htm Cheers Andy Brenner wrote: 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, Microsoft MVP - Excel http://www.andypope.info |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to graph (or draw) a vertical line on the chart
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... -- Regards, John "Jerry" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I draw a tangent line on a graph ? | Excel Discussion (Misc queries) | |||
Draw a horizontal bar and line graph | Excel Discussion (Misc queries) | |||
draw vertical line to mark today in diagram | Excel Discussion (Misc queries) | |||
How do i draw a regression line on a scatter graph | Charts and Charting in Excel | |||
Can I draw a tangent to a line in graph in Excel? | Charts and Charting in Excel |