+ Reply to Thread
Results 1 to 2 of 2

[SOLVED] problem with VBA to draw vertical lines

  1. #1
    Wazooli
    Guest

    [SOLVED] 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

  2. #2
    Greg Wilson
    Guest

    RE: 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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1