I'm plotting chronological data in Excel and want to look more closely at
some points. I' like to put the cursor on a point (and the tooltip gives me
the date), right-click on it, and open a macro that gives me more information
about that date. How would I, for example, right-click on a data point and
choose a menu item "Show date" that comes out with a message box, "The date
is " & [date]?

Hi Mike,

What you're describing is certainly doable, however it will take a fair bit
of coding to acheive.

To respond to clicks on a chart, you will need to use the chart's MouseDown
event. If the chart is on it's own chart sheet, it's events are ready to go,
and can be accessed in the same manner as normal worksheets - by
right-clicking on the sheet's tab then selecting View Code. If your chart is
embedded on a worksheet, it get's a little trickier. You need to set up a
special Class Module to enable it's events. There is a VBA help topic titled
"Using events with embedded charts" that will walk you through the process.

Once you're able to respond to the MouseDown event, you can take that
event's X,Y parameters, and pass them to the GetChartElement method, which
will tell you which data point was clicked. It will be an index into the
Points collection of one of the chart's Series. That same index can be used
to look up the corresponding cell in the chart's source data range. A simple
offset from this cell should give you the point's date. You would need to
store this date in a public variable so you could access it later when you
click your customised menu button.

It's a help if you already know the chart's source data range (typically,
from when you created the chart). If you don't already know the source data
range, you have to parse it from the Series' Formula property.

As for the menu, you could add a button to Excel's Series CommandBar as
follows.
(The Series CommandBar is the one you see when you right click on a chart's
data point.)

Dim CBC As CommandBarControl
Set CBC = Application.CommandBars("Series").Controls.Add
CBC.Caption = "Show Date"
CBC.OnAction = MyShowDateMacro


So, it's not a trivial solution, however there's nothing there that can't be
done.


Regards,
Vic Eldridge



"MikeM" wrote:

I'm plotting chronological data in Excel and want to look more closely at
some points. I' like to put the cursor on a point (and the tooltip gives me
the date), right-click on it, and open a macro that gives me more information
about that date. How would I, for example, right-click on a data point and
choose a menu item "Show date" that comes out with a message box, "The date
is " & [date]?

Thanks, Vic. It looks like this should do it, except that I'm unclear on how
to follow the VBA instructions you referenced. As per the help instructions,
I created a module and named it EventClassModule, and added the line "Public
WithEvents myChartClass As Chart" to it.

Then it states, "After the new object has been declared with events..." but
what is the new object? I assume that I declare it in my Module1 module,
where the rest of my code sits, but I have no clue as to how to do it. The
book I'm using (Excel 2003 VBA Programmer's Reference) has an index entry for
"Declare," but it's for subs and functions.

So -- I now have a new class, myChartClass. I guess I have to create an object
in this class, and then assign it to the chart somehow. Then I can assign
the MouseDown event to the new command bar entry "Show Date." Am I on the
right track?

TIA,
Mike
"Vic Eldridge" wrote:

Hi Mike,

What you're describing is certainly doable, however it will take a fair bit
of coding to acheive.

To respond to clicks on a chart, you will need to use the chart's MouseDown
event. If the chart is on it's own chart sheet, it's events are ready to go,
and can be accessed in the same manner as normal worksheets - by
right-clicking on the sheet's tab then selecting View Code. If your chart is
embedded on a worksheet, it get's a little trickier. You need to set up a
special Class Module to enable it's events. There is a VBA help topic titled
"Using events with embedded charts" that will walk you through the process.

Once you're able to respond to the MouseDown event, you can take that
event's X,Y parameters, and pass them to the GetChartElement method, which
will tell you which data point was clicked. It will be an index into the
Points collection of one of the chart's Series. That same index can be used
to look up the corresponding cell in the chart's source data range. A simple
offset from this cell should give you the point's date. You would need to
store this date in a public variable so you could access it later when you
click your customised menu button.

It's a help if you already know the chart's source data range (typically,
from when you created the chart). If you don't already know the source data
range, you have to parse it from the Series' Formula property.

As for the menu, you could add a button to Excel's Series CommandBar as
follows.
(The Series CommandBar is the one you see when you right click on a chart's
data point.)

Dim CBC As CommandBarControl
Set CBC = Application.CommandBars("Series").Controls.Add
CBC.Caption = "Show Date"
CBC.OnAction = MyShowDateMacro


So, it's not a trivial solution, however there's nothing there that can't be
done.


Regards,
Vic Eldridge



"MikeM" wrote:

I'm plotting chronological data in Excel and want to look more closely at
some points. I' like to put the cursor on a point (and the tooltip gives me
the date), right-click on it, and open a macro that gives me more information
about that date. How would I, for example, right-click on a data point and
choose a menu item "Show date" that comes out with a message box, "The date
is " & [date]?

Hi Mike,

I'm not sure I can explain it any better than the help file does.

Then it states, "After the new object has been declared with events..." but
what is the new object? I assume that I declare it in my Module1 module,
No, you've already declared it by adding the line "Public WithEvents
myChartClass As Chart" to the top of the class module. Once you've down
that, a new object (named myChartClass) appears in the left hand side drop
down list box. If you select that, all the chart events appear in the right
hand side drop down list box.

Then I can assign the MouseDown event to the new command bar entry
"Show Date." Am I on the right track?
Not really. In the chart's mousedown event, you work out the date of the
clicked data point and store it in a public variable for later use. (This
date will be needed later if and when you click on the custom menu button.)


Regards,
Vic Eldridge





"MikeM" wrote:

Thanks, Vic. It looks like this should do it, except that I'm unclear on how
to follow the VBA instructions you referenced. As per the help instructions,
I created a module and named it EventClassModule, and added the line "Public
WithEvents myChartClass As Chart" to it.

Then it states, "After the new object has been declared with events..." but
what is the new object? I assume that I declare it in my Module1 module,
where the rest of my code sits, but I have no clue as to how to do it. The
book I'm using (Excel 2003 VBA Programmer's Reference) has an index entry for
"Declare," but it's for subs and functions.

So -- I now have a new class, myChartClass. I guess I have to create an object
in this class, and then assign it to the chart somehow. Then I can assign
the MouseDown event to the new command bar entry "Show Date." Am I on the
right track?

TIA,
Mike
"Vic Eldridge" wrote:

Hi Mike,

What you're describing is certainly doable, however it will take a fair bit
of coding to acheive.

To respond to clicks on a chart, you will need to use the chart's MouseDown
event. If the chart is on it's own chart sheet, it's events are ready to go,
and can be accessed in the same manner as normal worksheets - by
right-clicking on the sheet's tab then selecting View Code. If your chart is
embedded on a worksheet, it get's a little trickier. You need to set up a
special Class Module to enable it's events. There is a VBA help topic titled
"Using events with embedded charts" that will walk you through the process.

Once you're able to respond to the MouseDown event, you can take that
event's X,Y parameters, and pass them to the GetChartElement method, which
will tell you which data point was clicked. It will be an index into the
Points collection of one of the chart's Series. That same index can be used
to look up the corresponding cell in the chart's source data range. A simple
offset from this cell should give you the point's date. You would need to
store this date in a public variable so you could access it later when you
click your customised menu button.

It's a help if you already know the chart's source data range (typically,
from when you created the chart). If you don't already know the source data
range, you have to parse it from the Series' Formula property.

As for the menu, you could add a button to Excel's Series CommandBar as
follows.
(The Series CommandBar is the one you see when you right click on a chart's
data point.)

Dim CBC As CommandBarControl
Set CBC = Application.CommandBars("Series").Controls.Add
CBC.Caption = "Show Date"
CBC.OnAction = MyShowDateMacro


So, it's not a trivial solution, however there's nothing there that can't be
done.


Regards,
Vic Eldridge



"MikeM" wrote:

I'm plotting chronological data in Excel and want to look more closely at
some points. I' like to put the cursor on a point (and the tooltip gives me
the date), right-click on it, and open a macro that gives me more information
about that date. How would I, for example, right-click on a data point and
choose a menu item "Show date" that comes out with a message box, "The date
is " & [date]?

Hi Vic,

Got it!! Many thanks -- and I now have a better understanding of how classes
work.

Mike

"Vic Eldridge" wrote:

Hi Mike,

I'm not sure I can explain it any better than the help file does.

Then it states, "After the new object has been declared with events..." but
what is the new object? I assume that I declare it in my Module1 module,
No, you've already declared it by adding the line "Public WithEvents
myChartClass As Chart" to the top of the class module. Once you've down
that, a new object (named myChartClass) appears in the left hand side drop
down list box. If you select that, all the chart events appear in the right
hand side drop down list box.

Then I can assign the MouseDown event to the new command bar entry
"Show Date." Am I on the right track?
Not really. In the chart's mousedown event, you work out the date of the
clicked data point and store it in a public variable for later use. (This
date will be needed later if and when you click on the custom menu button.)


Regards,
Vic Eldridge





"MikeM" wrote:

Thanks, Vic. It looks like this should do it, except that I'm unclear on how
to follow the VBA instructions you referenced. As per the help instructions,
I created a module and named it EventClassModule, and added the line "Public
WithEvents myChartClass As Chart" to it.

Then it states, "After the new object has been declared with events..." but
what is the new object? I assume that I declare it in my Module1 module,
where the rest of my code sits, but I have no clue as to how to do it. The
book I'm using (Excel 2003 VBA Programmer's Reference) has an index entry for
"Declare," but it's for subs and functions.

So -- I now have a new class, myChartClass. I guess I have to create an object
in this class, and then assign it to the chart somehow. Then I can assign
the MouseDown event to the new command bar entry "Show Date." Am I on the
right track?

TIA,
Mike
"Vic Eldridge" wrote:

Hi Mike,

What you're describing is certainly doable, however it will take a fair bit
of coding to acheive.

To respond to clicks on a chart, you will need to use the chart's MouseDown
event. If the chart is on it's own chart sheet, it's events are ready to go,
and can be accessed in the same manner as normal worksheets - by
right-clicking on the sheet's tab then selecting View Code. If your chart is
embedded on a worksheet, it get's a little trickier. You need to set up a
special Class Module to enable it's events. There is a VBA help topic titled
"Using events with embedded charts" that will walk you through the process.

Once you're able to respond to the MouseDown event, you can take that
event's X,Y parameters, and pass them to the GetChartElement method, which
will tell you which data point was clicked. It will be an index into the
Points collection of one of the chart's Series. That same index can be used
to look up the corresponding cell in the chart's source data range. A simple
offset from this cell should give you the point's date. You would need to
store this date in a public variable so you could access it later when you
click your customised menu button.

It's a help if you already know the chart's source data range (typically,
from when you created the chart). If you don't already know the source data
range, you have to parse it from the Series' Formula property.

As for the menu, you could add a button to Excel's Series CommandBar as
follows.
(The Series CommandBar is the one you see when you right click on a chart's
data point.)

Dim CBC As CommandBarControl
Set CBC = Application.CommandBars("Series").Controls.Add
CBC.Caption = "Show Date"
CBC.OnAction = MyShowDateMacro


So, it's not a trivial solution, however there's nothing there that can't be
done.


Regards,
Vic Eldridge



"MikeM" wrote:

I'm plotting chronological data in Excel and want to look more closely at
some points. I' like to put the cursor on a point (and the tooltip gives me
the date), right-click on it, and open a macro that gives me more information
about that date. How would I, for example, right-click on a data point and
choose a menu item "Show date" that comes out with a message box, "The date
is " & [date]?

I wrote an article about chart events which you may find useful:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"MikeM" wrote in message
...
Hi Vic,

Got it!! Many thanks -- and I now have a better understanding of how
classes
work.

Mike

"Vic Eldridge" wrote:

Hi Mike,

I'm not sure I can explain it any better than the help file does.

Then it states, "After the new object has been declared with events..."
but
what is the new object? I assume that I declare it in my Module1
module,
No, you've already declared it by adding the line "Public WithEvents
myChartClass As Chart" to the top of the class module. Once you've down
that, a new object (named myChartClass) appears in the left hand side
drop
down list box. If you select that, all the chart events appear in the
right
hand side drop down list box.

Then I can assign the MouseDown event to the new command bar entry
"Show Date." Am I on the right track?
Not really. In the chart's mousedown event, you work out the date of the
clicked data point and store it in a public variable for later use.
(This
date will be needed later if and when you click on the custom menu
button.)


Regards,
Vic Eldridge





"MikeM" wrote:

Thanks, Vic. It looks like this should do it, except that I'm unclear
on how
to follow the VBA instructions you referenced. As per the help
instructions,
I created a module and named it EventClassModule, and added the line
"Public
WithEvents myChartClass As Chart" to it.

Then it states, "After the new object has been declared with events..."
but
what is the new object? I assume that I declare it in my Module1
module,
where the rest of my code sits, but I have no clue as to how to do it.
The
book I'm using (Excel 2003 VBA Programmer's Reference) has an index
entry for
"Declare," but it's for subs and functions.

So -- I now have a new class, myChartClass. I guess I have to create an
object
in this class, and then assign it to the chart somehow. Then I can
assign
the MouseDown event to the new command bar entry "Show Date." Am I on
the
right track?

TIA,
Mike
"Vic Eldridge" wrote:

Hi Mike,

What you're describing is certainly doable, however it will take a
fair bit
of coding to acheive.

To respond to clicks on a chart, you will need to use the chart's
MouseDown
event. If the chart is on it's own chart sheet, it's events are
ready to go,
and can be accessed in the same manner as normal worksheets - by
right-clicking on the sheet's tab then selecting View Code. If your
chart is
embedded on a worksheet, it get's a little trickier. You need to set
up a
special Class Module to enable it's events. There is a VBA help topic
titled
"Using events with embedded charts" that will walk you through the
process.

Once you're able to respond to the MouseDown event, you can take that
event's X,Y parameters, and pass them to the GetChartElement method,
which
will tell you which data point was clicked. It will be an index into
the
Points collection of one of the chart's Series. That same index can
be used
to look up the corresponding cell in the chart's source data range. A
simple
offset from this cell should give you the point's date. You would
need to
store this date in a public variable so you could access it later
when you
click your customised menu button.

It's a help if you already know the chart's source data range
(typically,
from when you created the chart). If you don't already know the
source data
range, you have to parse it from the Series' Formula property.

As for the menu, you could add a button to Excel's Series CommandBar
as
follows.
(The Series CommandBar is the one you see when you right click on a
chart's
data point.)

Dim CBC As CommandBarControl
Set CBC = Application.CommandBars("Series").Controls.Add
CBC.Caption = "Show Date"
CBC.OnAction = MyShowDateMacro


So, it's not a trivial solution, however there's nothing there that
can't be
done.


Regards,
Vic Eldridge



"MikeM" wrote:

I'm plotting chronological data in Excel and want to look more
closely at
some points. I' like to put the cursor on a point (and the tooltip
gives me
the date), right-click on it, and open a macro that gives me more
information
about that date. How would I, for example, right-click on a data
point and
choose a menu item "Show date" that comes out with a message box,
"The date
is " & [date]?

Thanks, Jon. I haven't had a chance to read it thoroughly, but it seems to
answer a lot of the questions I have about chart events.

Mike

"Jon Peltier" wrote:

I wrote an article about chart events which you may find useful:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"MikeM" wrote in message
...
Hi Vic,

Got it!! Many thanks -- and I now have a better understanding of how
classes
work.

Mike

"Vic Eldridge" wrote:

Hi Mike,

I'm not sure I can explain it any better than the help file does.

Then it states, "After the new object has been declared with events..."
but
what is the new object? I assume that I declare it in my Module1
module,
No, you've already declared it by adding the line "Public WithEvents
myChartClass As Chart" to the top of the class module. Once you've down
that, a new object (named myChartClass) appears in the left hand side
drop
down list box. If you select that, all the chart events appear in the
right
hand side drop down list box.

Then I can assign the MouseDown event to the new command bar entry
"Show Date." Am I on the right track?
Not really. In the chart's mousedown event, you work out the date of the
clicked data point and store it in a public variable for later use.
(This
date will be needed later if and when you click on the custom menu
button.)


Regards,
Vic Eldridge





"MikeM" wrote:

Thanks, Vic. It looks like this should do it, except that I'm unclear
on how
to follow the VBA instructions you referenced. As per the help
instructions,
I created a module and named it EventClassModule, and added the line
"Public
WithEvents myChartClass As Chart" to it.

Then it states, "After the new object has been declared with events..."
but
what is the new object? I assume that I declare it in my Module1
module,
where the rest of my code sits, but I have no clue as to how to do it.
The
book I'm using (Excel 2003 VBA Programmer's Reference) has an index
entry for
"Declare," but it's for subs and functions.

So -- I now have a new class, myChartClass. I guess I have to create an
object
in this class, and then assign it to the chart somehow. Then I can
assign
the MouseDown event to the new command bar entry "Show Date." Am I on
the
right track?

TIA,
Mike
"Vic Eldridge" wrote:

Hi Mike,

What you're describing is certainly doable, however it will take a
fair bit
of coding to acheive.

To respond to clicks on a chart, you will need to use the chart's
MouseDown
event. If the chart is on it's own chart sheet, it's events are
ready to go,
and can be accessed in the same manner as normal worksheets - by
right-clicking on the sheet's tab then selecting View Code. If your
chart is
embedded on a worksheet, it get's a little trickier. You need to set
up a
special Class Module to enable it's events. There is a VBA help topic
titled
"Using events with embedded charts" that will walk you through the
process.

Once you're able to respond to the MouseDown event, you can take that
event's X,Y parameters, and pass them to the GetChartElement method,
which
will tell you which data point was clicked. It will be an index into
the
Points collection of one of the chart's Series. That same index can
be used
to look up the corresponding cell in the chart's source data range. A
simple
offset from this cell should give you the point's date. You would
need to
store this date in a public variable so you could access it later
when you
click your customised menu button.

It's a help if you already know the chart's source data range
(typically,
from when you created the chart). If you don't already know the
source data
range, you have to parse it from the Series' Formula property.

As for the menu, you could add a button to Excel's Series CommandBar
as
follows.
(The Series CommandBar is the one you see when you right click on a
chart's
data point.)

Dim CBC As CommandBarControl
Set CBC = Application.CommandBars("Series").Controls.Add
CBC.Caption = "Show Date"
CBC.OnAction = MyShowDateMacro


So, it's not a trivial solution, however there's nothing there that
can't be
done.


Regards,
Vic Eldridge



"MikeM" wrote:

I'm plotting chronological data in Excel and want to look more
closely at
some points. I' like to put the cursor on a point (and the tooltip
gives me
the date), right-click on it, and open a macro that gives me more
information
about that date. How would I, for example, right-click on a data
point and
choose a menu item "Show date" that comes out with a message box,
"The date
is " & [date]?