Full Version: Finding X Axis Series Values From Chart Object Control In VBA
UtterAccess Forums > Microsoft® Access > Access Forms
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 encorporate 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)
    'remove first series

Tests = ser.XValues
MsgBox Tests(3)

The delete for the seriescollection works fine, 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 conditionallt 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 recieved!

I should add that I comment out the series delete bit when testing the return of xvalues, otherwise there would be nothing there to find smile.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.