Finding X Axis Series Values From Chart Object Control In VBA, Access 2010

[Scottish_Anatomist]

Hey everyone,
I'm trying to find a way to access the x axis category values on a chart created from a query (and the y axis ones).
Although there is a wealth of information about manipulating the chart object in excel, I've been unable to find much about doing it in access and viewing the references available not all of it applies to access for some reason.

I can achieve the effect in excel with the following VBA code to return the 3rd category value:

Sub GraphRead()
Dim cht As Chart
Dim ser As Series
Dim Tests As Variant

    'Set chart and series collection 
    Set cht = Worksheets("Sheet1").ChartObjects("Chart 1").Chart
    Set ser = cht.SeriesCollection("Profit")

Tests = ser.XValues
MsgBox Tests(3)
End Sub

However the same code, altered to incorporate the microsoft graph reference, doesn't function in access:

Dim cht As Graph.Chart
Dim ser As Graph.Series

Set cht = Me.Graph.Object
Set ser = cht.SeriesCollection(1)
   

Tests = ser.XValues
MsgBox Tests(3)

Some things work in Access, for example the delete for the seriescollection works fine:

'remove first series
ser.Delete

but i just can't find a way to read the value of the x and y values for each data point. The reason I want to is to conditionally format the bar columns on the chart to a certain colour depending on a) the category value and b) if the value is above/below a specified value.
Any help with be gratefully received!
Thanks,

Jimmy

 

[JHB]

As you found out, graph objects isn't so easy to manipulate in MS-Access.
But can't you use the rowsource for the graph to decide what colour a certain data-point should have, instead of reading the data-point from the graph object.
I've attached an example.