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