Update PivotChart Legands
 

Hi All,

I want to update Chart Legands for a PivotChart Using VBA. Currently
my pvtChart is based on Pivot that looks like
DefectSource Count
Code 10
Requirement 5
Design 2

and currently the Chart Legands Shows values as {Code , Requirement,
Design}. I wish to Change them to {Code (10) , Requirement (5), Design
(2)}.

I could do it using the below proc(added at the end of discussion) for
the charts that are NOT based on PivotTables.
But the same proc fails in line "Ser.XValues = arrLegand" if my source
data is based on any PivotTable. I am not even able to update that
manually because Axis Labels are not editable.

I am using XL2007, Please suggest if there is any way to acheive
this.

Any Help in this regard is highly appreciated.
Thanks,
Avinash

Code:
Sub UpdateLegand()
Dim arrTempXVal()
Dim arrTempVal()
Dim arrLegand()
Dim NumElements As Integer

Set Ser = ActiveChart.SeriesCollection(1)

NumElements = UBound(Ser.XValues)

ReDim arrTempXVal(0 To NumElements - 1)
ReDim arrTempVal(0 To NumElements - 1)
ReDim arrLegand(0 To NumElements - 1)

arrTempXVal = Ser.XValues
arrTempVal = Ser.Values

If InStr(1, arrTempXVal(1), "(", vbTextCompare) = 0 Then
For i = 1 To NumElements
arrLegand(i - 1) = arrTempXVal(i) & " (" & arrTempVal(i) &
") "
Next i
Ser.XValues = arrLegand ' Here it fails
End If
ActiveChart.SetElement (msoElementLegendRight)

End Sub