You could set up chart events, such that clicking on a point activates another
chart. Here's an example, using chart sheets (embedded charts require a class module
and a longer explanation).
Data for Chart 1:
a 3
b 4
c 5
Make three more charts on chart sheets, and name them 'a', 'b', and 'c' (to match
the category labels in Chart 1). Right click on the Chart 1 sheet tab, and select
View Code. A code module appears in the VB Editor, which is where you put code that
responds to chart events.
Choose Chart from the dropdown in the top left, and choose MouseDown from the
dropdown on the top right. This event procedure outline appears in the code module:
Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As
Long, ByVal y As Long)
End Sub
This macro runs when a mouse button is depressed, and various arguments are passed
to the macro. Button lets you get fancy with left and right clicks, Shift lets you
deal with shift or alt or ctrl keys, and X and Y are the position of the click.
People try to use this X and Y, but it's not easy. Fortunately, you can just pass
these parameters to another function:
GetChartElement(x As Long, y As Long, ElementID As Long, Arg1 As Long, Arg2 As Long)
In go X and Y, and out come ElementID (the chart element under the mouse), Arg 1 (an
additional argument, for example, series number if ElementID indicates a series),
and Arg 2 (point number if ElementID is a series).
Then we link the selected point to the target chart through the point's category and
the target chart's sheet name. Here's the entire procedure:
Private Sub Chart_MouseDown(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
Dim v As Variant
GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = xlSeries Then
v = ActiveChart.SeriesCollection(Arg1).XValues
Charts(v(Arg2)).Activate
End If
End Sub
When you click on the chart, the macro goes into action, determines where you
clicked, what is under the click, and if it's a series, which point was it. It looks
up the category label corresponding to that point, then activates the chart named
for that label.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
