How to add a vertical line on a chart? 1

[feipezi]

Hi,

I tried to add a vertical line on a chart to represent a cutoff point or a benchmark. It will be dynamic, i.e., it will change position with the change of data on X axis.

I found some hint like

ActiveSheet.Shapes.AddConnector(msoConnectorStraight, 10, 10, 250, 250).Select

But it gave me a tilt line, not a straight line. Any idea how to make it straight? Like modify the parameters?

I know what I'm looking for is not a connector but as long as I can get a straight line, fine.

Thanks in advance.

 

[SkipVought]

Hi,

If you just want a LINE that you can move manually, just insert it manually.

If you want a "line" as described in your first paragraph, then make a SERIES that can be PLOTTEED on the chart.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Thanks Skip.

I understand what you suggested. I found some examples from Jon Peltier's webpage. But the problem is I have to set up a straight line data row or column. It's like a pain in the neck.

But worth trying.

Thanks again.

 

[SkipVought]

straight line data row or column

That makes no sense! Please explain what you mean.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

I was following your idea of adding a vertical line on a chart.

Here is what my data looks like:

Test_Cntl -6 -5 -4 -3 -2 -1 1 2 3

TEST 2.31545 2.28849 2.11074 2.36310 2.21455 2.29629 2.22503 2.18116 2.31045

CNTL 2.33816 2.28416 2.10368 2.34673 2.20748 2.26168 2.26475 2.15487 2.31083

cutoff 0 (the vertical line is supposed to be between -1 and 1).

The X-axis would be Test_Cntl values; Y-axis would range from 1.9 to 2.4 depending on TEST and CNTL.

As I tried to add 'cutoff' series, I was supposed to see 'Series X-Values' and 'Series Y-Values' for you to fill in. But all I got was 'Series Values' in the 'Edit Series' window. Did I miss anything?

Joh Peltier's tips would only be working on 2003 Version, not 2007.

I wrote a macro based on a recorded one but still cannot set up the vertical line. Here is the macro:


Sub UpdateCharts()
Application.ScreenUpdating = False
Dim ChtOb As ChartObject
Dim SourceRng As Range
On Error Resume Next
ActiveSheet.ChartObjects(1).Delete
On Error GoTo 0
Sheets("Sheet8 (3)").Activate
Set SourceRng = Range("a1:j3")
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
'MsgBox SourceRng.Select
.SetSourceData Source:=SourceRng, PlotBy:=xlRows
.ChartType = xlLineMarkers
.Axes(xlCategory).Select
.Axes(xlValue).Select
'On Error GoTo Escape
.SeriesCollection(1).Delete
.SeriesCollection(1).XValues = "='Sheet8 (3)'!$B$1:$J$1"
'.SeriesCollection.NewSeries
'.SeriesCollection(2).XValues = "='Sheet8 (3)'!$M$2:$M$2"
.SetElement (msoElementLegendNone)
.SetElement (msoElementDataTableWithLegendKeys)
.DataTable.Font.Size = 6.5
....
....
End With
ActiveChart.Parent.RoundedCorners = True
Set ChtOb = ActiveChart.Parent
ChtOb.Height = 480
ChtOb.Width = 800
ChtOb.Top = 116
ChtOb.Left = 2
Cells(1, 1).Activate
'Escape: Exit Sub
Application.ScreenUpdating = True
End Sub


Please advise.

Thanks in advance.

 

[SkipVought]

First, your chart will work better as a SCATTER chart (with or without lines)

cutoff 0 (the vertical line is supposed to be between -1 and 1).

and that would be ZERO, Yes???

You need a SEPARATE table for your LINE series. Mine is
[tt]
x y
-1
0 0
0 -6
0 3
0 0
1
[/tt]
This series goes on the SECONDARY axis.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Thanks. Let me try that. I cannot do SCATTER chart since it's not what the users wanted.

take care.

 

[SkipVought]

A SCATTER chart can connect points with LINES.

What are the users ACTUAL requirements?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,

My problem is I cannot even be able to manually set up a V line. The line I got is like a letter "V" upside down, i.e., there is always 2 points at X axis.

The users specifically want Line, not Scatter XY. So I have no other choice.

What they want was to set up a V line at 0 to compare the changes in Test and Control. I think they run the process bi-weekly so the V line will move with the time passing.

Hope I made myself clear enough.

Thanks again.

 

[SkipVought]

Did you notice the data for my VERTICAL line?
[tt]
x y
-1
0 0
0 -6
0 3
0 0
1
[/tt]
There is NO ^ shaped line. I it VERTAICAL from -6 to 3!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Forgive me but I still cannot get the V line based on your data.

Here is what I did. Please tell me what's wrong I did.

1) select your data: 2 columns and 7 rows;

2) click Insert and pick Line chart;

3) pick Format Data Series from dropdown;

4) select y column data as Secondary Axis;

....

The graph is not what expected since X column data won't show on X axis; Y column not on Y axis.

What did I do wrong?

Thanks again