I'm trying to set the chart legend entries in a ready set up Access Chart ( Access 14.0 or Access 12.0) with references set up for Ms Graph ( 14.0 or 12.0 ) where I am changing the record source for different situations.

In an earlier post I've managed to set the chart title via code.

Here's the code:

Dim mychart As Graph.Chart
Set mychart = Me.Graph0.Object
mychart.ChartTitle.Caption = "New Title" ' Works

mychart.Legend.LegendEntries(1).text = "Peter" ' Fails Object does not support this property or method
mychart.SeriesCollection(0).text = "Peter" ' Fails Unable to get series collection property of class

Can someone please show me how to change this object. Thanks.

Peter

Somehow, I never needed to set the Legend for the Series but I found that I had a statement to set the Legend for a TrendLine of the Series as follows:

Thus, I suspect that we can't actually set the Legend Text/Caption for a particular Series (or TrendLine of a particular Series) directly and we have to change the Name of the Series which automatically changes the Legend Text/Caption.

Have you tried:

mychart.SeriesCollection(0).Name = "Peter"

?

Thanks for this but I'm afraid:

mychart.SeriesCollection(0).Name = "Peter"

fails with 1004 "Unable to get SeriesCollection property of the Chart class" .

Having scanned the Graph object library more carefully, I can't see a Text or Caption property. Which ties in with what you say.
So I returned to the query which feeds the chart ( RowSource) and created Aliases for each of the Legend entries -
which are actually the same as the default query field names. I.e for a query field with Name Peter I've amended
this to Peter:Peter. Now instead of SumOfPeter appearing in the legend I get Peter - which is what I want. It
appears you can set the alias to the same as the field name - as long as its exactly the same i.e Peter: [Peter] & "John" would not be acceptable Access SQL.

I don't know if this the best or only way round this - I'd feel more comfortable finding a property I could set -
but at least I have an interim solution.

So if anyone has a better idea, please let me know. Thanks

Peter

It sounds to me that you found the correct solution, actually. I did a bit more digging after I posted for the following:

For Excel 2003, I found that unlike the TrendLine, we cannot change the Name of the Series either because it is not exposed for manipulation (perhaps, I should have checked before my last post). From Excel VBA Help topic "LegendEntry Object":

Thus, what you need to do is to use Alias in the DataSource for the SeriesCollection to supply the correct name/LegendEntry Text which eliminates the need to change the LegendEntry Text. This is what I did in my Access Query that supplies the data to the Chart and hence there is nothing in my code to change the legend text for the Series, only for the TrendLine.

This may be different in Access 2010 / Graph 14 but I'll leave it for you to check your Access / Excel 2010 Help.