Graph (Chart) - Set Color of Data Point with VBA

[June7]

New adventure in VBA programming of charts on Access report. I am trying to programmatically set the color (and maybe pattern and texture) of data segments for stacked column chart. Can't get it to go beyond the first data point. Triggers error 'Unable to get the Points property of the Series class'. NOTE: Detail section Format event only runs in PrintPreview or direct to printer.

Here is the procedure:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim chtObj As Object
Dim j As Integer
Dim strType As String
Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long, c5 As Long
c1 = RGB(100, 100, 100)
c2 = RGB(100, 250, 250)
c3 = RGB(100, 200, 100)
c4 = RGB(200, 200, 100)
c5 = RGB(250, 100, 100)
Set chtObj = Me.gphHole.Object
For j = 1 To 5
    strType = chtObj.SeriesCollection(1).Points(j).DataLabel.Text
    chtObj.SeriesCollection(1).Points(j).Interior.Color = _
         Switch(strType = "OL", c1, strType = "GP-GM(S)", c2, strType = "BLDRCBBL", c3, strType = "SPG", c4, strType = "TILL", c5)
Next
End Sub

If I set the For loop as 1 To 1, the first point color will change. This code distilled from found example, which is also in the report code module of attached db.

[rpeare]

I also don't see where you're calling the function to do the coloring in your code, just the two functions Display_Format and CodeSample. How are you calling the function?

[June7]

Format is an event procedure, in this case, of the report Detail section. The event runs when the report opens in PrintPreview or direct to printer output.

Not using the CodeSample procedure, it is there just for information. It is code I found on web which I used as guide to build Format event.

[rpeare]

Dim chtObj As Object
Dim j As Integer
Dim strType As String
Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long, c5 As Long

c1 = RGB(100, 100, 100)
c2 = RGB(100, 250, 250)
c3 = RGB(100, 200, 100)
c4 = RGB(200, 200, 100)
c5 = RGB(250, 100, 100)

Set chtObj = Me.gphHole.Object

For j = 1 To 5
    strType = chtObj.SeriesCollection(j).Points(1).DataLabel.Text
    Debug.Print strType
    chtObj.SeriesCollection(j).Points(1).Interior.Color = Switch(strType = "OL", c1, strType = "GP-GM(S)", c2, strType = "BLDRCBBL", c3, strType = "SPG", c4, strType = "TILL", c5)
Next

it's a little counter intuitive your j should be in your series collection your points is 1

[June7]

That does seem to be improvement. Thank you, I never would have thought. Maybe the difference is due to it being a stacked column and not a cluster bar chart or I just misinterpreted the sample code.

And my upper limit should be 6, not 5, for this example.

[rpeare]

I actually thought you were doing a snippet and were counting the records in your data source and use that to determine the upper bound of j rather than hard coding it, that way you could handle any number of elements.

[June7]

That's my next step, to make code dynamic. I didn't need from that code sample the part about populating the graph's data table. And I thought the arrays were a bit much. I wanted to test with simple code and small dataset. First, had to determine that setting the point properties could be accomplished in run-time (hooray, it works!).

I will have a table associating color code with soil type (a lot more than 5). And if I get really ambitious, will try to set data point pattern or texture or use a picture. I am trying to replicate a graphing utility in a proprietary software (executable only) our geologists use to model borehole strata.