How do I create dynamic graphs?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Do you know if there is a way to make an Excel chart dynamic so a user can
analyze information graphically? For example, create a pie chart, then click
on a slice, which then renders additional slices based on the “children†of
the original (aka “Parentâ€) slice?
 
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/
_______
 
Back
Top