Capture Events on programmatically generated Charts
 

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

Capture Events on programmatically generated Charts
 

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)


"INTP56" wrote in message
...
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

Capture Events on programmatically generated Charts
 

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"INTP56" wrote in message
...
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

Capture Events on programmatically generated Charts
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Chip Pearson" wrote in message
...
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)


"INTP56" wrote in message
...
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