Accessing Tooltip Data in an Excel Chart

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]?
>
>
>