Chart events

[PO]

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

 

[PO]

P.S. I'm using Excel 2003 sp2

 

[Peter T]

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