chart colour issue

[Aoife]

Hi all, 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
    With Me.Graph0
        For i = 1 To .SeriesCollection.Count

            With .SeriesCollection(i)
                .Interior.Color = DLookup("ColumnColour", _
                                          "tblColumnColours", _
                                          "ColumnText = " & Chr(34) & .Name & Chr(34))
            End With
        Next i
    End With
    
    End Sub

 

[JHB]

Have a look in the attached database, I think it is exact what you want.

 

[Aoife]

Thank you so much.

Your prompt and comprehensive solution has ultimately led me to cancel my Exp's Exchange subscription (posted same problem over a week ago with little in the way of assistance), saving me almost £14 a month and lots of my hair.

Keep up the great support

 

[JHB]

You're welcome, good luck.