Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart events
Hi
I have a XY-Scatter chart embedded in a worksheet. I have applied an autofilter to the charts data table. There are two problems I'm trying to solve: 1. When the user changes the filter I want to run code to update the chart labels. How do I trap the "filter_change" event? 2. When the user double-clicks one of the points in the chart I want a message box to display the rownumber in the worksheet from where the point gets its data. How can this be achieved? TIA Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart events
P.S. I'm using Excel 2003 sp2
"PO" <h skrev i meddelandet ... Hi I have a XY-Scatter chart embedded in a worksheet. I have applied an autofilter to the charts data table. There are two problems I'm trying to solve: 1. When the user changes the filter I want to run code to update the chart labels. How do I trap the "filter_change" event? 2. When the user double-clicks one of the points in the chart I want a message box to display the rownumber in the worksheet from where the point gets its data. How can this be achieved? TIA Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart events
1. AFAIK normally changing the filter does not trigger an event. However you
can subclass the chart and trap its calculate event, which indirectly gets triggered when you change the filtered chart data (as does any other change to chart data). The event stub is below, I'll leave you to sort out looking for filter changes. 2. Also in the chart events you can trap double-click on a point. Insert a class module named Class1 - ' normal module Private mClsChart As Class1 Sub StartChartEvents() Set mClsChart = New Class1 Set mClsChart.cht = ActiveSheet.ChartObjects(1).Chart End Sub Sub StopChartEvents() Set mClsChart = Nothing End Sub ''' end module1 code ' class module named Class1 Public WithEvents cht As Chart Private Sub cht_BeforeDoubleClick(ByVal ElementID As Long, _ ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Dim nRow As Long Dim x, y Dim s As String Dim sr As Series If ElementID = xlSeries And Arg2 0 Then Cancel = True ' prevent the format dialog Set sr = cht.SeriesCollection(Arg1) With sr x = .XValues(Arg2) y = .Values(Arg2) End With nRow = Range("XValues" & Arg1).Row + Arg2 - 1 s = "X=" & x & " : Y=" & y & vbCr & "see row " & nRow MsgBox s End If End Sub Private Sub cht_Calculate() Debug.Print "cht_Calculate" 'look at new filter here End Sub '' end class1 code I named the range of X-values "XValues1", you don't need to use Names of course as long as you can associate the correct range-reference to the series number as returned bu 'Arg1' Run StartChartEvents to start trapping events, maybe call that & StopChartEvents from sheet activate/deactivate events. (You'll need to restart after editing code) Regards, Peter T "PO" <h wrote in message ... Hi I have a XY-Scatter chart embedded in a worksheet. I have applied an autofilter to the charts data table. There are two problems I'm trying to solve: 1. When the user changes the filter I want to run code to update the chart labels. How do I trap the "filter_change" event? 2. When the user double-clicks one of the points in the chart I want a message box to display the rownumber in the worksheet from where the point gets its data. How can this be achieved? TIA Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart for events per unit of time??? | Charts and Charting in Excel | |||
Chart Events... | Excel Programming | |||
Chart Events | Excel Programming | |||
enabling chart events for an embedded chart | Excel Programming | |||
How can I track the chart mouse events? | Excel Programming |