Target or Cross Hair Cursor that "Snaps to Point"

[Robert Harley]

If you have a dataseries in the form of a line on an XYScatter Chart
(for example daily prices), would it be possible to create a CrossHair
or Target cursor. This would a horizontal line representing the Y and
a vertical line representing the X.

You would then need to be able to drag this target cursor around the
chart, whilst the relevant coordinates updated in a couple of cells or
a text box.

Thirdly you would want to be able to make this cursor "Snap To Point"
(a Reuter term) to the relevant data series, so that when you dragged
the vertical arm across the chart, the horizontal arm would jump to
the relevant point.

Many Thanks,
Robert Harley.

 

[Jon Peltier]

Hi Robert -

This kind of thing can be done using VBA. You need to set up chart
events, which requires a class module. Here's the official word:
http://support.microsoft.com/default.aspx?scid=kb;[LN];161858

I've posted a simple working example of code that gets the value of a
clicked point here, without too much documentation:
http://www.geocities.com/jonpeltier/Excel/Zips/GetXYonAnyChart.zip

You would set up the embedded chart to raise events, then when the chart
is activated (a chart event) you change the cursor to the cross. When
it's deactivated (another event), you change it back. When the mouse
moves, the event procedure tells you the current X and Y of the cursor,
in points from the top left corner of the chart. Using the dimensions
of the chart (specifically PlotArea.InsideLeft, .InsideTop,
..InsideWidth, and .InsideHeight) and your finest 8th grade algebra, you
determine the X and Y of the cursor, in chart axis coordinates. These
numbers can be placed into cells or a text box. Knowing the X and Y of
the cursor, you can determine the Y for the same X for your data series.
I'm not sure exactly how to draw the line, because there are several
options. One way is to have a single point data series under the
cursor, and use Hi-Low lines to connect the point to the other series.
Or make an error bar for this extra point. Or drop an error bar from
the point on the main series to the cursor. Or hey, just draw, move,
and resize an actual line from point A to point B.

You might want to use the approach with a separate one-point series with
an error bar. Use a cross as the marker for this point, and only move
it when the mouse button is down, but lock it in place when the button
is raised. So you're clicking and dragging only when you want, and not
just dragging without an opportunity to freeze the cross.

- Jon