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