Specifying Color of Columns in MS Access Histogram Chart

Question

I have a dynamic histogram (column chart) using the Modern Chart object in Access that functions just fine. However, the interior color of the columns are diffierent with each series selection by the user using the combo box at the top right of the form.(see figures below). Figure 1. Figure 2.

From my research, there are significant limitations to what I can control using formatting options available in VBA for Access. Microsoft's Support site lists a property for Chart objects called ChartSeries, which in turn, has a FillColor property. The syntax for use is: *expressions*.ChartSeries.FillColor where expression represents a Chart object. When I attempt to use those properties, I get "Run-time error 438: Object doesn't support the property or method." for the statement .ChartSeries.FillColor = lngBlue. My code is below.

Private Sub cbo_ShipType_Change()

    Dim strSQL As String, cht As Chart, lngBlack As Long, lngBlue As Long
    
    lngBlack = RGB(0, 0, 0)
    lngBlue = RGB(0, 0, 256)
    
    Set cht = Me.cht_ShipType
    strSQL = "SELECT tbl_ChartData.[YR], " & cbo_ShipType & " FROM tbl_ChartData"
    cht.RowSource = strSQL
    With cht
        .ChartTitle = cbo_ShipType & " Fleet Size"
        .PrimaryValuesAxisFontSize = 14
        .PrimaryValuesAxisFontColor = lngBlack
        .CategoryAxisFontSize = 12
        .CategoryAxisFontColor = lngBlack
        .ChartSeries.FillColor = lngBlue
        .Requery
    End With

End Sub

Does anyone know if or how I can adjust the colors of my data series columns?

Answer 1

Thanks! The Modern Chart object doesn't support the SeriesCollection object. Instead, it uses the ChartSeriesCollection object which has slightly different (and fewer) properties. After adding the Microsoft Office 16.0 Object Library, I did at the following code based on your suggestion to fix my problem:

For j = 1 To cht.ChartSeriesCollection.Count - 1
    cht.ChartSeriesCollection(j).FillColor = vbBlue
Next j