[SOLVED] Use the "mouse over" event to exclude a data point from a series

I'm relatively new to excel macros and would greatly appreciate any
help.

In an Excel scatter chart, if I move the mouse over a data point,
a "tool-tip text" or a "hover text" appears.

Is there a way to intercept this event programmatically to exclude the
data point from the series and then have the chart re-drawn?

Randall -

I just wrote an article about chart events in Excel (mouse-overs, etc.), which
doesn't tell you exactly how to exclude a point, but it can at least tell you which
point it was, and some more detailed code could remove it. The article is in the
winter Computor Companion on-line magazine:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Randall Hiltz wrote:

> I'm relatively new to excel macros and would greatly appreciate any
> help.
>
> In an Excel scatter chart, if I move the mouse over a data point,
> a "tool-tip text" or a "hover text" appears.
>
> Is there a way to intercept this event programmatically to exclude the
> data point from the series and then have the chart re-drawn?

Thanks Jon.

I've printed your article and will give it a try. It may take a day or
two as I'm fumbleing my way through macro development.
I'll let you know my progress.

Randy

I fear Jon's comment may mislead you on the complexity of the task at
hand. I, for one, would not undertake this except as a paid assignment
and even then with a lot of caveats to address various issues, some of
which are highlighted below.

You have to do a whole bunch of things, none of which is simple. Not
that they cannot be done, just that this is not an easy project.

Of course, even before we get started, you have to define what
'exclude' means. Do you show fewer points or do you leave a hole? If
you show fewer points and there are multiple series in the chart what
is the consequence for them? Anyway, assuming all the definitional
issues are resolved...

First, you will have to identify the point that has been clicked.
Jon's article will help there.

For the rest of the work below, you will have to deal with the x and y
sources (and for a bubble chart the size source) in a coordinated
fashion.

Now, you need to know the source of the series. Unfortunately, there
is no easy way to do this. You will have to parse the SERIES formula.
The code in John Walkenbach's tip (http://j-
walk.com/ss/excel/tips/tip83.htm) forms a great starting point. I
suspect -- but am not sure -- that you will have to enhance John's code
to deal with your specific case.

OK, now that you have the source for the series, figure out which item
in the source corresponds to the clicked point.

Next, if the source is a literal array, it will be relatively easy to
exclude the value. Of course, this is subject to the definition of
exclude as discussed above.

If the source is a range, you will have to subtract the cell
corresponding to the clicked point from the current range. Again,
while possible, not trivial. Lacking a XL/VBA function, various people
have shared code for this including a neat little trick using a new
worksheet from Tom Ogilvy. You will have to search the google.com
archives of the XL NGs to get the code.

If the source is a named range/formula, you are SOL. I cannot think of
any safe and reliable way to muck with it.

Finally, you have to set the series to this new range/array. This is
the easiest part.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Thanks Jon.
>
> I've printed your article and will give it a try. It may take a day or
> two as I'm fumbleing my way through macro development.
> I'll let you know my progress.
>
> Randy
>
>

Thanks Tushar.

"Exclude" in this context means to remove the point from the data
series and redraw the charts. We are attempting to deal with errant
points that are annomalies in our data series. The series includes
serveral hundred/thousand points which originate in an engineering
application and are used to produce approx 700 charts. Because of the
volume, our Engineers would prefer to visually remove the points in
question rather than wade through the data series.

Jon's suggestion works in that it allows me to intercept the event.
However, I am exactly in the state you predicted and with my limited
Excel VBA experience, I am beginning to wonder if Excel is the right
tool or to your point, is this a project that requires a experienced
"commercial" developer.
Any thoughts would be greatly appreciated.

Best Regards
R. Hiltz

Excel *may* not be the right tool, not because it cannot do what you
want but because of certain strange restrictions in its charting
module.

One of the restrictions that might come back to bite you is that the
string describing the values in a series cannot be longer than about
250 characters. So, if you remove a lot of cells from a range, the
length will keep on expanding. For example, start with Sheet1!$A$1:$A
$10 and remove A3. That will yield Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$10.
Now, remove A8 to get Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$7,Sheet1!$A$9:$A
$10. You get the idea. Can you work around it? Sure, but it will
take additional programming -- and may include restrictions on how
easily the chart can be updated with new data (if that is an issue).

Would I recommend you do this on your own? What you want to do is a
very interesting and intriguing idea, but ultimately, it depends on how
comfortable you are with programming, with object oriented programming,
with event programming, with XL. And, of course, how you value the
time-cost trade-off.

For example, I recently completed a quick project for a pharmaceutical
company. It involved creation of a custom-radar chart -- something for
which I already have instructions and code on my web site
(http://www.tushar-mehta.com/excel/so...adar/index.htm).
However, the company wanted a professionally implemented system that
was fully automated from both the Windows desktop and the Windows
scheduler. New incoming data would be in CSV files with the final
output being an image of the chart left in the clipboard. Could
someone in that organization have done it? I am sure the answer should
be yes. Yet, it was obviously more cost-effective for them to work
with me.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Thanks Tushar.
>
> "Exclude" in this context means to remove the point from the data
> series and redraw the charts. We are attempting to deal with errant
> points that are annomalies in our data series. The series includes
> serveral hundred/thousand points which originate in an engineering
> application and are used to produce approx 700 charts. Because of the
> volume, our Engineers would prefer to visually remove the points in
> question rather than wade through the data series.
>
> Jon's suggestion works in that it allows me to intercept the event.
> However, I am exactly in the state you predicted and with my limited
> Excel VBA experience, I am beginning to wonder if Excel is the right
> tool or to your point, is this a project that requires a experienced
> "commercial" developer.
> Any thoughts would be greatly appreciated.
>
> Best Regards
> R. Hiltz
>
>

Hi Randall,

> "Exclude" in this context means to remove the point from the data
> series and redraw the charts. We are attempting to deal with errant
> points that are annomalies in our data series. The series includes
> serveral hundred/thousand points which originate in an engineering
> application and are used to produce approx 700 charts. Because of the
> volume, our Engineers would prefer to visually remove the points in
> question rather than wade through the data series.

If you're comfortable with deleting the source data for that point, you
should be OK; Tushar's replies point out the problems if you want to
keep the source data intact, but just not display it on the chart.

Personally, I would respond to the point being clicked, rather than just
a mouse over. Clicking a point will fire the _Select event and Jon's
article shows how to respond to that. Furthermore, if you use defined
names to link your chart to your data, the code for the _Select event
could be as simple as:

Private Sub Chart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

'Is a point selected?
If ElementID = xlSeries And Arg2 > 0 Then
Application.EnableEvents = False
Sheet1.Range("chtXData").Cells(Arg2).EntireRow.Delete
Application.EnableEvents = True
End if

End Sub

which assumes there's only one series and the source data X values have
been given the defined name "chtXData".

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

As I was walking away from my desk after the 2nd rambling post, I
thought of something similar which would still protect the data -- just
hide that row (and, of course, set the appropriate option to only plot
visible cells).

Made me feel kinda stupid for making a mountain out of a molehill.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, [email protected]
says...
> Hi Randall,
>
> > "Exclude" in this context means to remove the point from the data
> > series and redraw the charts. We are attempting to deal with errant
> > points that are annomalies in our data series. The series includes
> > serveral hundred/thousand points which originate in an engineering
> > application and are used to produce approx 700 charts. Because of the
> > volume, our Engineers would prefer to visually remove the points in
> > question rather than wade through the data series.
>
> If you're comfortable with deleting the source data for that point, you
> should be OK; Tushar's replies point out the problems if you want to
> keep the source data intact, but just not display it on the chart.
>
> Personally, I would respond to the point being clicked, rather than just
> a mouse over. Clicking a point will fire the _Select event and Jon's
> article shows how to respond to that. Furthermore, if you use defined
> names to link your chart to your data, the code for the _Select event
> could be as simple as:
>
> Private Sub Chart_Select(ByVal ElementID As Long, _
> ByVal Arg1 As Long, ByVal Arg2 As Long)
>
> 'Is a point selected?
> If ElementID = xlSeries And Arg2 > 0 Then
> Application.EnableEvents = False
> Sheet1.Range("chtXData").Cells(Arg2).EntireRow.Delete
> Application.EnableEvents = True
> End if
>
> End Sub
>
> which assumes there's only one series and the source data X values have
> been given the defined name "chtXData".
>
> Regards
>
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
>
>
>

Hi Tushar,

> just
> hide that row (and, of course, set the appropriate option to only plot
> visible cells).

Good one!

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Thanks. Hiding is worth a try.

I was about to suggest making a copy of the chart (add a copy of the series to the
chart), with the data dumped into a new blank sheet. Then delete rows as needed. But
Tushar's suggestion to hide the undesired rows seems best.

The trick with two series on the chart, one for all data and one for data to
include, is that you can see both series. Click on a point in the "include" series
to exclude it, or click on a point in the "exclude" ("all data") series to include it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Tushar Mehta wrote:

> As I was walking away from my desk after the 2nd rambling post, I
> thought of something similar which would still protect the data -- just
> hide that row (and, of course, set the appropriate option to only plot
> visible cells).
>
> Made me feel kinda stupid for making a mountain out of a molehill.
>