go from one chart to another

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

Guest

I am using the same data that Darell posted...since what
I want is similar yet....

I have 20 departments with 4 regional managers looking
after them.

example:
Title: Area Managers League
AREAMANAGER1:
Town1 Results
Town2 Results
etc
AREAMANAGER2:
Town1 Results
Town2 Results
etc


I have created one chart with all the area managers, 4
others showing the departments for each manager(all Pie
charts). Is it possible to click on the particular area
manager and go to the particular area manager's sub chart?

Thanks
 
You can use a class module in VBA to make your program respond to events
in the chart. The following are two (poorly documented, sorry) examples
that do this, the easy way for chart sheets, slightly more difficult for
embedded charts:

http://peltiertech.com/Excel/Charts/chartvba.html#PointInfo

There are zip files with VBA code in the workbooks. There is some
documentation of chart events here:

How to Trap Events for an Embedded Chart
http://support.microsoft.com/?kbid=213738

Event Sequences in Microsoft Excel 97 - Chart Object Events
http://www.microsoft.com/exceldev/articles/xlevnts.htm#chart

Chart object events using VBA in Microsoft Excel
http://www.exceltip.com/show_tip/Ev..._events_using_VBA_in_Microsoft_Excel/439.html


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
D'oh! Pressed send before I finished the thought.

Using my examples or the other ones I cited, capture the mousedown event
on one of the charted points, and use the click to activate the chart
based on which point was clicked on.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
I have the following code on one of the points
Sheets("OH").SeriesCollection(1).Points(5).OnAction =
Sheets("FCLS").Activate
and it does not work. Do I need to deactivcate the
default that takes one to formatting a chart?

Thanks
 
The code that activates the other sheet must be linked to one of the
mouse events in the class module that enables events in the chart. On
Action is meaningless for a point.

The mouse event in my workbooks looks like this:

Private Sub EmbChart_MouseUp _
(ByVal Button As Long, ByVal Shift As Long, _
ByVal X As Long, ByVal Y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Double, myY As Double

If Button = xlPrimaryButton Then
With EmbChart
.GetChartElement X, Y, ElementID, Arg1, Arg2

If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)

MsgBox "Series " & Arg1 & vbCrLf _
& """" & .SeriesCollection(Arg1).Name & """" _
& vbCrLf& "Point " & Arg2 & vbCrLf _
& "X = " & myX & vbCrLf _
& "Y = " & myY & vbCrLf & vbCrLf _
& "Any questions?"
End If
End If
End With
End If
End Sub

Replace the myX=, myY=, and message box lines with code that decides
which sheet to activate depending on the series (Arg1) and point (Arg2)
were selected.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top