Load ws cell data into a textbox via click on chart datapoint

[a]

Not sure if this is a chart question or programming question.

If I have data in a worksheet like this:
A B C
1 Date Number Text
2 2 Dec 2000 234 Sunny Day
3 1 Dec 2000 117 Wrecked My Car

and I create a line chart, I'd like to be able to hover or click the mouse
on the data point in the chart and have the text from column C load into a
textbox (the text may be many paragraphs long).

Are there any ways to do this, either with VBA, C# or other techniques?

Thanks, Paul

 

[Jon Peltier]

Tushar Mehta has a utility that does something like this. Look for Auto
Chart Hover on his website (http://tushar-mehta.com). Otherwise you'll need
to use some kind of chart events to detect when you have moused over
something significant, and populate the textbox accordingly. For a primer on
chart events, look here:

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

- Jon

 

[a]

Thanks Jon. I tried Tushar's utility and am waiting for his answer to some
questions about it...can you tell me what object I need to use in order to
grab the contents of the column ("C") adjacent to the column ("B") that
contains the data for the datapoint on the graph?

Thanks, Paul

---------------------------------------------

 

[Jon Peltier]

Uh, that's more than just one object. You need to figure out what cell the
point's data comes from, then find the cell offset by the correct amount,
then put this cell's content into the textbox. So that's a chart, a series,
a point, a series formula to parse, a worksheet, some ranges, a textbox,
plus a bunch of code to tie them together.

- Jon

 

[a]

Jon:

I'm going out of town for a week, but I'll work on this when I get back and
I hope you'll be willing to help if I get stuck.

Thanks for listing the pieces to the solution for me.

Paul

-----------------------------------

 

[Kevin Lawson]

Class for Hover Text

Hi Paul

I faced this problem recently and wrote a class to deal with it, ClassEvents (attached). This makes calls to John Walkenbach's ChartSeries class to parse the chart series, so your project needs to include his class module available here: http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/

The idea was originally based on code which Andrew Poulsom wrote: http://www.mrexcel.com/forum/showthread.php?t=45004 but modified for the MouseMove event. You use the class by including code such as the following:

Dim myChartEvents As New ChartEvents

Private Sub Chart_Activate()
myChartEvents.Chart = ActiveChart
myChartEvents.HoverData = Array(-2, " ", -1)
End Sub

In this example, cells with offsets of -2 and -1 columns from the chart's X-Values data contain the first name and second name respectively corresponding to the data point for that person. (nb: The ChartEvents class is coded to handle charts displaying only filtered data and still display the correct hover data).

Hope this is useful

Kevin