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

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" <(E-Mail Removed)> wrote in message
news:B3E5BDFD-10F3-49BF-B73C-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:B3E5BDFD-10F3-49BF-B73C-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:BD1159B9-BCA0-409A-AB00-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:B3E5BDFD-10F3-49BF-B73C-(E-Mail Removed)...
>> 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
>>
>