Click on a chart bar to drill into source data

[Ronanm]

Hi

Does anyone know if it's possible to have a chart so that when any of the series(Bars) are clicked, it will drill into the source data. Not a pivot chart, but similiar to the Pivot table where it opens a new sheet and displays the data.

Thanks

Ronan

 

[RoryA]

What do you mean by 'drill into'? For a bar, there is only one data point, and you can see that from its tooltip.

 

[Ronanm]

Hi

Thanks for tha reply

E.g. Let's say there is a chart with one os the columns showing 19 employees between the age of 30 -45.
Is there a way that the column can be clicked on which will result in the 19 employees that the column relates to showing...
And thinking about it, it would have to be a Pivot Table, and therefore a Pivot Chart to be able to drill through to the underlying data....

Should I re-ask the question in the forum stating a Pivot chart?

Regards

ronan

 

[RoryA]

No need to re-ask - I think it's clear enough now. I'll have a think.

 

[Ronanm]

Many thanks Rory...

Ronan

 

[Ronanm]

Got it...

Use the code below, and put in the the Chart section of VBA. Needs to be a "Pivot Chart"...

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

 

[spushparani]

hi i used the code given by you its helpful but it takes multiple click and shows the data in multiple sheet, it would be great if you can help me in getting the code for drilling the data with the sheet name & only once the data should be clicked on the chart and the drill down should take place