VBA to control Access 2010 Line Chart Marker color

Question

I have created a pivot line chart in Access 2010. I am trying to use VBA to format the lines and markers so that my charts all look the same regardless of the series name. I can get everything to work except changing the marker color. Here is what I am using....

    Private Sub Form_Load()

'Comp 1
Me.ChartSpace.Charts(0).SeriesCollection(0).Line.Color = RGB(0, 128, 255)
Me.ChartSpace.Charts(0).SeriesCollection(0).Line.Weight = 6
Me.ChartSpace.Charts(0).SeriesCollection(0).marker.Size = 6
Me.ChartSpace.Charts(0).SeriesCollection(0).marker.Style = 1
Me.ChartSpace.Charts(0).SeriesCollection(0).MarkerForegroundColor = vbRed

I have tried many different iterations for the marker color but I keep getting "run-time error '438': Object doesn't support this property or method" no matter how I try it. Any assistance is appreciated.

I updated code to

Private Sub Form_Load()

Dim p As Long

'Comp 1
With Me.ChartSpace.Charts(0).SeriesCollection(0)
    .Line.Color = RGB(99, 66, 255)
    .Line.Weight = 6
    .marker.Size = 8
    .marker.Style = 2

pc = .Points.Count

For p = 1 To pc
    .Points(p).MarkerForegroundColorIndex = vbRed
Next
End With

but still have the same problem. I added the pc = .points.count to verify accuracy. Everything works except changing the marker color.

Not sure it's the same in Access, but try using the Point.MarkerForeGroundColor (https://msdn.microsoft.com/en-us/library/office/ff834722.aspx) — David Zemens, May 02 '17 at 14:34
latest iteration `Me.ChartSpace.Charts(0).SeriesCollection(0).Point.MarkerForegroundColor = vbRed` — Michael Byars, May 02 '17 at 14:58
You need to specify a point by index, e.g., `.Points(0).MarkerForegroundColor` etc. and may therefore need to do a loop over the `Points` collection. — David Zemens, May 02 '17 at 15:00

score 1 · Answer 1 · answered May 02 '17 at 15:03

You can try:

Dim p as Long
With Me.ChartSpace.Charts(0).SeriesCollection(0)
    .Line.Color = RGB(0, 128, 255)
    .Line.Weight = 6
    .marker.Size = 6
    .marker.Style = 1
    For p = 1 to .Points.Count
        .Points(p).MarkerForegroundColor = vbRed
    Next
End With

The MarkerForeGroundColor is a property of a Point object, not a Series object. Documentation from Excel, but it should be the same across different Office Applications with VBA:

https://msdn.microsoft.com/en-us/library/office/ff834722.aspx

score 0 · Answer 2 · answered May 02 '17 at 15:26

I got it!! Thanks David Zemens for pointing me in the right direction. I just had to tweek the .points line and now it works perfectly. One note, I also needed to adjust the For line since Access is 0 based. Here is the final code. Thanks all.

With Me.ChartSpace.Charts(0).SeriesCollection(0)
    .Line.Color = RGB(99, 66, 255)
    .Line.Weight = 6
    .marker.Size = 8
    .marker.Style = 2

pc = .Points.Count

For p = 0 To pc - 1
    .Points(p).Interior.Color = vbRed
Next
End With

VBA to control Access 2010 Line Chart Marker color

2 Answers2