VBA Pie Chart Legend - How to set legend text?

Question

A similar question was asked (Set Legend Text Pie Chart) but the solution doesn't work for me, maybe I'm missing something. I'm fairly new to working with VBA/Excel/Access and reports and I'm on a limited deadline so I am hoping somebody can enlighten me.

What I am doing is populating a range of cells with values pulled from an access database and then programatically generating pie charts based on these cell values. The pie charts are fairly simple and contain 2 pieces of data. I am generating 1-4 of them based on the users' selections. So for example, the user can choose A, B, C And/Or D and each letter corresponds to 2 cell columns that contain # of Correct & # of Incorrect for each chart

The reports are being generated inside of a loop (1 to 4) corresponding to A/B/C/D The Code I'm using to create the charts looks like this:

Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Cells(20,shift(shiftIndex)), Cells(21, shift(shiftIndex))
ActiveChart.HasLegend = True

Which is saying to use the range B20:B21, C20:C21, etc for the Correct/Incorrect values. The problem is that the legend is showing "1" & "2" as labels and I want it to show "Correct" & "Incorrect"

In the other question, the person suggested using the syntax:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("b6:c6," & "b" & x & ":c" & x)

Where b6 would contain "Correct" and c6 would contain "Incorrect" in my case for my labels but when I use this:

ActiveChart.SetSourceData Source:=Sheets("Sheet 1").Range("a26:a27, a20:a21")

Where a26 contains "Correct", a27 contains "Incorrect" and a20 & a21 contain my correct and incorrect values - it tries to use all 4 values in my pie chart. Am I missing something here? Separating the parameters by a comma should indicate the first range as my legend and the second as my data source?

Answer 1

you can directly control this using the XValues property of the Series object...

Assuming you have only one series in the chart try:

Activechart.Seriescollection(1).XValues=Activesheet.Range("a26:a27")

a further tip would be to do this:

Dim chMyChart as Chart 
Dim chMySeries as Series

Set chMyChart = Activesheet.Activechart 
Set chMySeries = chMyChart.Seriescollection(1)

and then use chMyChart in place of Active chart and chMySeries in place of ActiveChart etc.

This is more efficient, but it also activates vba's Intellisense that shows you a list of all of the properties available for these objects after you enter the ".". For me anyway, this doesn't seem to be exposed on objects like Active??? or indexed objects like chMyChart.SeriesCollection(1)