VBA to control Access 2010 Line Chart Marker color

0
votes

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.

2
vbams-access-2010pivot-chart
Not sure it's the same in Access, but try using the Point.MarkerForeGroundColor (msdn.microsoft.com/en-us/library/office/ff834722.aspx) – David Zemens
same issue as before – Michael Byars
Update your code with the attempt you're using? – David Zemens
latest iteration Me.ChartSpace.Charts(0).SeriesCollection(0).Point.MarkerForegroundColor = vbRed – Michael Byars
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

2 Answers

1
votes

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

0
votes

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