Changing name of item in chart legend using VBA

[Statistician]

Hi folks

Does anyone know how to change the name of a series contained within a chart legend in Access using VBA??

I currently have a form with a list box. The user selects 2 months. This then drives a query which drives the report (chart). I cannot change the query field headings to reflect the different month's selection

Basically I need the legend to show the selection made and at the moment they only show the generic title within the query.

Can anyone help or even understand my problem?

Cheers

 

[genomon]

OK - THINK I did something like this last year....
I am pasting code here that references a chart object:
Please see additional comments at end of code...
With chtChartBlockC
.ChartData = arrMUNByLacGroup 'array containing vals
.ColumnCount = 3
.ColumnLabelCount = 3
.Legend.Location.LocationType =VtChLocationnTypeTopRight

'THIS ONE I COULD NEVER GET QUITE RIGHT
'.Legend.LegendEntry(1).LegendKey.MarkerStyle = VtMarkerStyleFilledUpTriangle
.Column = 1
.ColumnLabel = "ALL COWS"
.Column = 2
.ColumnLabel = "1st LAC"
.Column = 3
.ColumnLabel = "LATER LAC"
.Refresh
End With

I think you probably need to change some settings in the legend object (the one I never got quite right here!). I bet if you do a search on charts and legend properties & methods, you will comme up with what you need! Hope this helped to point you in the right direction or at least gave you new ideas to search for help on.
-Geno

 

[mischief911]

Does this help? One way I have been able to troubleshoot dynamically modifiying excel charts in code is to create an excel macro as I perform the chart modification. I then open up the macro and copy excel vba into Access with usually only slight modifications needed to get it to work in Access. Here is a sample I used to dynamically alter the chart legend in code:

ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.Legend.Select
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""DATA SERIES NAME"""