use mouseclick to dynamically change chart series? Chart_beforeDoubleClick?

[KR]

I'm trying to hack a solution to allow dynamic, graphical drill-down into a
data source. I'm thinking a pie chart, where you click on a wedge, and the
pie chart changes to show all the component pieces of that wedge, etc. I'd
prefer a pie chart embedded in a worksheet, but I could make do with a chart
sheet if needed. I can probably figure out how to dynamically update my
ranges, if someone can tell mehow to determine which data element has been
clicked.

e.g.: Chart shows Revenue from meat, fruit, dairy, breads
<click on dairy>
Chart now shows Revenue from milk, eggs, cheese, butter
<click on cheese>
Chart now shows Revenue from Cheddar, Mozerella, Colby, Swiss
<click on cheddar>
Chart now shows Revenue from sliced, bar, shredded (8oz), shredded (16oz)

I tried using the following code from a MS website, in the hopes that I
could eventually figure out which series & data point had been clicked, so I
could update the source data via VBA and update the graph, but it isn't
giving me a messagebox, so my clicking on the chart isn't having the
intended effect.

Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
MsgBox "Test"
End Sub

I'd appreciate any advice or pointers!
Thanks,
Keith

 

[Guest]

put in sheets kode window:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
MsgBox ("Double click on cells ") & ActiveCell.Address
End Sub

 

[KR]

While this code works for clicking on the worksheet, my goal is to do this
by clicking on the graph itself to drill down through the data.
So I'm still back to trying to figure out the following, until/unless anyone
can give other options?

Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
MsgBox "Test"
End Sub

Thanks!
Keith