I'd like to be able adjust the colour of a column chart (on a form) depending upon the series being graphed. ie Lab is blue, Office is green etc. There will only be 5 different series (areas) but the parameter query upon which the graph is based may have no data for the Lab in certain selected time periods, and the remaining areas will change colour.
I need assistance with what I assume will be a DLookup in order to make this work, automation error is generated at "With Me.Graph0.SeriesCollection
(i)" line upon opening the form. A run time error (1004 Unable to get the SeriesCollection property of the Chart class ) upon opening the form from design view is also generated by the same line - with 2 separate errors, was also wondering if the code is placed in the correct place.
I've referenced Microsoft Graph 15.0 Object Library.
Private Sub Graph0_Updated(Code As Integer)
'tblColumnColours contains 2 fields, ColumnText which is name of each series (ie Lab, Office, Workshop) and ColumnColour
Dim i As Integer
For i = 1 To .SeriesCollection.Count
.Interior.Color = DLookup("ColumnColour", _
"ColumnText = " & Chr(34) & .SeriesCollection(i) & Chr(34))