problem with VBA to draw vertical lines
 

I have an X-Y scatter plot as an embedded chart. I have a list of X & Y
values that I would like to use to draw vertical lines on the chart. The
range for x-series is AB10:AC33, and I would like to use the same values to
define the y-values (AD10:AE10). I started by recording a macro for adding
one series to the chart, then tried to generalize using a for...next loop for
adding all 24. This of course did not work. Here is my code:
Private Sub combobox_2_click()

Dim i As Integer
Dim num As Integer
Dim myRange As Range

If Range("focus_choice") = "All" Then

Application.ScreenUpdating = False
Worksheets(2).Activate
Set myRange = Range("chr_start")
num = ActiveSheet.ChartObjects(1).Chart.SeriesCollection .count


For i = 1 To 24
With ActiveSheet
..ChartObjects(1).Chart.SeriesCollection.NewSeries
..ChartObjects(1).Chart.SeriesCollection(1).Series (num +
1).XValues = myRange.Offset(i, 0)
..ChartObjects(1).Chart.SeriesCollection(1).Series (num +
1).Values = Worksheets(2).Range(Cells(30, 10), Cells(31, 10))
End With

With
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Series(num + 1).Border
..ColorIndex = vbWhite
..LineStyle = xlContinuous
End With

With
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Series(num + 1)
..MarkerStyle = xlNone
End With

Next i

End If

Application.ScreenUpdating = True

End Sub


This does not work because I get an error message that says the "object
doesn't support this method or property" for the second assignment after the
for i = 1 to 24 (.ChartObjects(1).Chart.SeriesCollection(1).Series (num +
1).XValues = myRange.Offset(i, 0)). I know this can be done, but I need
help, so ... please help.

Thanks in advance,

Warren

problem with VBA to draw vertical lines
 

The best way IMO to add vertical lines to a chart is to add a series where
the Y-values are all set to the desired maximum value and error bars are set
to 100% in the Y-direction.

Alternatively, each vertical line can be a separate series having two points
each where the X-values of each series (i.e. each bar) are the same and the
Y-values are respectively 0 and the desired max value. The X-values must of
course be different between series. Examples:
Series 1, Point 1: X = 5, Y = 0
Series 1, Point 2: X = 5, Y = 100
Series 2, Point 1: X = 7, Y = 0
Series 2, Point 2: X = 7, Y = 100
etc...

The following demos the first option. Required is that the X- and Y-Value
ranges be named "XVals" and "YVals" respectively.

Sub MakeVertLines()
Dim ws As Worksheet
Dim rng As Range, rng2 As Range
Dim s As Series

Set ws = Worksheets(2)
Set rng = ws.Range("XVals")
Set rng2 = ws.Range("YVals")
With ws.ChartObjects(1).Chart
Set s = .SeriesCollection.NewSeries
End With
s.XValues = rng
s.Values = rng2
s.MarkerStyle = xlNone
s.ErrorBar Direction:=xlY, Include:=xlMinusValues, _
Type:=xlPercent, Amount:=100
s.ErrorBars.Border.ColorIndex = 3

End Sub

Regards,
Greg


"Wazooli" wrote:

I have an X-Y scatter plot as an embedded chart. I have a list of X & Y
values that I would like to use to draw vertical lines on the chart. The
range for x-series is AB10:AC33, and I would like to use the same values to
define the y-values (AD10:AE10). I started by recording a macro for adding
one series to the chart, then tried to generalize using a for...next loop for
adding all 24. This of course did not work. Here is my code:
Private Sub combobox_2_click()

Dim i As Integer
Dim num As Integer
Dim myRange As Range

If Range("focus_choice") = "All" Then

Application.ScreenUpdating = False
Worksheets(2).Activate
Set myRange = Range("chr_start")
num = ActiveSheet.ChartObjects(1).Chart.SeriesCollection .count


For i = 1 To 24
With ActiveSheet
.ChartObjects(1).Chart.SeriesCollection.NewSeries
.ChartObjects(1).Chart.SeriesCollection(1).Series( num +
1).XValues = myRange.Offset(i, 0)
.ChartObjects(1).Chart.SeriesCollection(1).Series( num +
1).Values = Worksheets(2).Range(Cells(30, 10), Cells(31, 10))
End With

With
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Series(num + 1).Border
.ColorIndex = vbWhite
.LineStyle = xlContinuous
End With

With
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Series(num + 1)
.MarkerStyle = xlNone
End With

Next i

End If

Application.ScreenUpdating = True

End Sub


This does not work because I get an error message that says the "object
doesn't support this method or property" for the second assignment after the
for i = 1 to 24 (.ChartObjects(1).Chart.SeriesCollection(1).Series (num +
1).XValues = myRange.Offset(i, 0)). I know this can be done, but I need
help, so ... please help.

Thanks in advance,

Warren