Pivot chart, problem drilling through to underlying data.

[Ronanm]

Using the code below I can click on a Pivot Charts column and drill down to the data, but the Pivot Chart needs to be a "Pivot Chart" sheet.

I can't get it to work when I change the location of the "PivotChart" and embed it in an ordinary worksheet.

Does anyone know how I could manage that....


Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long


ActiveChart.GetChartElement x, y, ElementID, Arg1, Arg2

' If clicked, show detail
If ElementID = 3 Then
ActiveChart.PivotLayout.PivotTable.DataBodyRange. _
Cells(Arg2, Arg1).ShowDetail = True
ActiveSheet.Cells(2, 2).Select
ActiveWindow.FreezePanes = True
End If
ErrHandler:

If Err.Number <> 0 Then MsgBox _
"Chart_MouseUp - Error#" & Err.Number & vbCrLf & _
Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
On Error Resume Next
On Error GoTo 0

End Sub


Ronan