Changing Marker color in Excel 2002 vba

boulder_dude2000 · February 3rd, 2009, 03:20 PM

I'm stuck trying to change the marker colors on some line charts I've got. This code was written a while ago, and I'm trying to make reasonable use of what's there. I'm running into problems trying to use the .MarkerForegroundColor method. The routine below is called for every line I want to add to my line chart. The data points are passed in YValues. The chart is an OWC chartspace control. I get error 13 on the line "Set oSeries = chrtContainer.Charts(0).SeriesCollection.Add" when I try the code below. Can someone help me out? Thanks

Code:

Public Sub AddChartData(ByRef chrtContainer As ChartSpace, Caption As String, RowLabels As Variant, YValues As Variant)    
' Declare the necessary local variables    
Dim oSeries As Series    
Dim pt As Point    
' Add a series to collection    
Set oSeries = chrtContainer.Charts(0).SeriesCollection.Add    
' Set series properties    
With oSeries
        .Caption = Caption
        .SetData chDimCategories, chDataLiteral, RowLabels
        .SetData chDimValues, chDataLiteral, YValues
        .Type = chChartTypeLineMarkers
        For Each pt In .Points
            pt.MarkerBackgroundColor = RGB(0, 255, 0)  ' green           
            pt.MarkerForegroundColor = RGB(255, 0, 0)  ' red
        Next pt
    End With
End Sub

I call this routine with the following:Call AddChartData(Me.chrtResults, Caption, RowLabel, YValues)where Caption is a string, RowLabel and YValues are arrays.Thanks for any help you can provide...