Capture Events on programmatically generated Charts

[INTP56]

Excel 2003, I create a chart programmatically. Now I would like to capture
Click Events on that chart I just created. It doesn't seem I can put code
under that object via code.

My actual situation is I have run time data, and my customer and I have not
been able to come up with a programmatic way of reliably finding certain
sections of data to be used for further calculations. (His current method is,
"I look at it")

My thought was to display a graph of the data, using builtin excel tools,
and eventually let him pick points. Maybe an alt-click or something on a
series would pop up a menu where he could designate which one of three
sections this is. Based on that selection I could process N points around it
or whatever.

Since I'm still trying to figure out how to do this, any ideas on how I
could detect a click on a chart/series that doesn't exist at code time would
be appreciated.

Thanks, Bob

 

[Chip Pearson]

You can use the chart's events in the worksheet that contains the chart. For
example, in the Sheet1 module in the VBAProject for the workbook, use the
following code:

Public WithEvents CHT As Excel.Chart
Private Sub CHT_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As
Long, _
ByVal Arg2 As Long, Cancel As Boolean)
Cancel = True
''''''''''''''''''''''''''''''''''''''''''''
' YOUR CODE HERE
''''''''''''''''''''''''''''''''''''''''''''
MsgBox "You are here"
End Sub

Then you need to Set CHT to the chart:

Sub InitCHT()
Set Sheet1.CHT = Sheet1.ChartObjects(1).Chart
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Andy Pope]

Hi,

Have a read of Jon Peltier's article,
http://www.computorcompanion.com/LPMArticle.asp?ID=221

He also has a couple of files you can down load. Links can be found at
http://peltiertech.com/Excel/Charts/ChartIndex.html#E

Cheers
Andy

 

[Jon Peltier]

Hmmm, I hadn't realized I could do this on a sheet module (as opposed to a
standalone chart events class module). Very interesting. I already have a
project that could benefit. Thanks.

- Jon