Charts: Drawing lines

[sterlingjms]

I am trying to draw lines on a chart generate by a vb program in Excel. Attempting to use the .shapes.addline but I am having problems with formating the code correctly. In the end I am not sure this is the result I am after because I am really trying to draw lines based on a specific value making up the x axis of the chart, i.e scale from 0 to 100, I want to draw a straight line across the chart at 50.

 

[johnwm]

Is this separate from the Gridlines? or do you just want a gridline at a specific value? You can set gridline values in code

With ActiveChart.Axes(xlValue)
    .MajorUnit = 50
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting
Steam Engine enthusiasts
Steam Engine Prints

 

[cummings54]

Sub MacroSample1()
   Set myline = Chart2.Shapes.AddLine(117.33, 57.36, 515.19, 229.43)
End Sub
Sub MacroSample2()
  Set myline = Sheet1.ChartObjects("Chart 1").Chart.Shapes.AddLine(117.33, 57.36, 515.19, 229.43)
End Sub

use sample1 if the chart is a separate sheet
use sample1 if the chart is embedded in a worksheet

 

[sterlingjms]

Ok I got this to work but I actually don't want to change the major and minor scale. In addition to the grid/scale, I am trying to draw addtional lines across the chart at specific levels within the x axis. Example: vb builds the chart, the x-axis scale is from 1780 to 1820 increments of 5. I want to draw line across the chart at 1807 and at 1792 but still leave the x-axis untouched.

 

[yakpol]

Another way is to add data series to the chart to draw the lines x { 1807, 1807, "", 1792, 1792} y {-1000, 1000, "", -1000,1000}

 

[sterlingjms]

What object in excel would you use to add a data series?

 

[yakpol]

Here's Excel macro recording:

ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = "={1807,1807}"
ActiveChart.SeriesCollection(3).Values = "={-1000,1000}"

you may need to create a new series for each line. Good luck!

 

[sterlingjms]

adding new series in this manner works but the lines are drawing in a vertical format and not horizontal across the chart at the specificied point, e.g. 1807 from the previous example. I have tried to manipulate the formatting of these statements but have been unsuccessful.

Any additional thoughts?

 

[yakpol]

I guess, exchanging values between .Xvalues and .Values will give a horizontal line.