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?
0
krhiltz (4)
1/11/2005 6:39:58 PM
excel.charting 18370 articles. 0 followers. Follow

10 Replies
801 Views

Similar Articles

[PageSpeed] 53

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?

0
1/12/2005 2:45:12 AM
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

0
krhiltz (4)
1/13/2005 11:49:21 AM
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 <1105616960.978588.226570@z14g2000cwz.googlegroups.com>, 
krhiltz@uniongas.com 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
> 
> 
0
1/16/2005 8:52:42 PM
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

0
krhiltz (4)
1/17/2005 12:00:38 PM
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/software/custom_radar/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 <1105963237.984944.240180@f14g2000cwb.googlegroups.com>, 
krhiltz@uniongas.com 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
> 
> 
0
1/17/2005 5:49:02 PM
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


0
1/17/2005 6:21:56 PM
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 <VA.000002fc.0b995f4d@oaltd.co.uk>, stephen@oaltd.co.uk 
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
> 
> 
> 
0
1/17/2005 7:40:47 PM
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


0
1/17/2005 9:04:48 PM
Thanks.  Hiding is worth a try.

0
krhiltz (4)
1/19/2005 11:53:09 AM
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.
> 

0
1/19/2005 6:20:16 PM
Reply:

Similar Artilces:

Detecting mouse over CStatic bitmap
Hello I'm trying to detect when a mouse is over one of three CStatic bitmaps, in a OnMouse function. Unfortunately the OnMouse routine only seems to be called when the pointer isn't over a static object. Currently I can get around it by using Set/Release Capture, but is there a better way? Nicholas Nicholas wrote: > Hello > > I'm trying to detect when a mouse is over one of three CStatic bitmaps, in a > OnMouse function. Unfortunately the OnMouse routine only seems to be called > when the pointer isn't over a static object. > > Currently I ...

mouse over effect
I used to be able to use the mouse over effect to change the displayed picture on the website. I have forgotten how to do it. HELP Select the image in the page and use Format Behaviors - select the image swap from the task bar -- _____________________________________________ SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ] "Warning - Using the F1 Key will not break anything!" (-; _____________________________________________ "Bas" <Bas@discussions.microsoft.com> wrote in message news:853A1E23-E935-4D61-9D5C-104CEBD10C03@microsoft.com... ...

How do i make a jpeg pop up on mouse over?
I want to make staff photos pop up when you mouse over on an org chart.. I assume this is easy but dont know how to do it?! Not so easy if you don't write few line of code VBA or other. The idea is - make your shape, - insert your image , - transform the shape and the image to a group - make the image of the group invisible with a user action a popup menu (without line of code, only with section action) drag mouse event over the shape mananging the mouse x and y (need line of code and not so easy) "leigh sanders" <leigh sanders@discussions.micro...

Mouse Over Effect
I have created a page where 90% of my black text turns red when you place the mouse over it. All of a sudden the mouse over effect has disappeared. I all I have been doing is linking the text to different PDF..I am not sure how I possibly deleted the mouse over effect throughout the whole site. Any idea?! Thank you Rendering problems are difficult to impossible to diagnose without seeing the page and associated CSS, image and Javascript files. But in this case, look for broken anchor or link tags, particularly the missing </a> part. A complete anchor/link tag resembles...

What message is posted when moving mouse over CEdit?
What message is posted when moving mouse over CEdit? I would like to implament a tooltio which is displayed when the mouse is moved over a control derived of CEdit. Therefore I would like to ask you if someone know what message I have to capture in order to call my finction displayinn the tooltip? Can someone help? And perhaps someone knows what should be done to enable tooltips for controls derived of CEdit? I have a dialog and implement the following method: BOOL COptionPricingFooterForm::OnToolTipNotify(UINT id, NMHDR *pNMHDR, LRESULT *pResult) everything works fine as long as my memebe...

Message popup when mouse over field
Is there any way to popup the definition from the table when someone navigates their mouse over that field in the form? I have been asked if this is possible but i do not know how to do it. Is there a way or is there an alternative/better way? Thanks. You can use the ControlTipText Property of the text box to add a description that will pop up when the mouse pointer move over the text box -- Good Luck BS"D "scott04" wrote: > Is there any way to popup the definition from the table when someone > navigates their mouse over that field in the form? I have been a...

Mouse Over
Hi I use a ownerdraw button. Is there any item state to identify Mouse Over . This will be much helpful to change bitmap during mouse over the button. thnx for that you have to subclass button Check out this article for more http://www.codeproject.com/miscctrl/subclassdemo.asp -- With Regards Alok Gupta Visit me at http://alok.bizhat.com "I Believe this will Help" "Sudharsan" <Sudharsan@discussions.microsoft.com> wrote in message news:B97CE65F-CABF-4F0B-A09C-094865D10D52@microsoft.com... > Hi > I use a ownerdraw button. > Is th...

Excel charts to appear once i mouse over a certain cell???
i have an excel chart, a scatter diagram, which is working perfectly fine as it is. I just want to know if there is any way in which I can put this chart in a comment or click a button which will display the chart and ensure that the chart gets updated as the input values change??? Is this possible Hi ace, >i have an excel chart, a scatter diagram, which is working perfectly fine >as > it is. I just want to know if there is any way in which I can put this > chart > in a comment or click a button which will display the chart and ensure > that > the chart gets upda...

On mouse over a button, change a picture elsewhere.
I have a button that when I mouse over it, I want to change a jpg elsewhere on the page. How would I do that? Would I use Javascript or Ajax? TIA - Jeff. "Mr. Magic" <Mufasa@NoWhere.Com> wrote in news:uJ4UTUYjKHA.4872@TK2MSFTNGP05.phx.gbl: > I have a button that when I mouse over it, I want to change a jpg > elsewhere on the page. How would I do that? Would I use Javascript or > Ajax? Standard JavaScript works fine in most cases. Here is one example: http://snurl.com/tysic Peace and Grace, -- Gregory A. Beamer (MVP) Twitter: @gbworld ...

Excel charts when i mouse over a cell
Folks, This is the situation. I have two worksheets, sheet 1 is called WA sheet 2 is called CA now CA is basically a summary sheet where all values are referenced off WA and i need graphs to display in CA based on values of WA. The graphs must be such that when i click on a certain cell, a certain graph pops up. and once i click some cell, the previous graph hides itself and considering the newly clicked cell, either nothing happens or a different graph pops up as the case may be. I had tried this mouse over graph thingy based on a solution that I had gotten here but the thing is it wa...

tooltip without a mouse over it?
Hi, I have tooltips working in my statusbar. Currently they work when I place the mouse over them (like they're supposed to). I'm wondering if its possible to trigger them to appear briefly when the mouse isn't over them. This'll be useful to me because it can indicate that a state has changed in the program without being too intrusive. Any ideas on how i can do this? Brett. Hi, I think it can be possible. See in windows all the controls are derived from the class CWnd. So the user defined controls also derived from that class only. Now the current situation is if we mo...

Help with Mouse Over
I have checked the 'Page Properties - Advanced' - The Hyperlink Rollover Effect has been checked on ALL pages. It only works on some of the pages. I am unable to find anything different with the setting. Any idea? Thank You! Need Links the Pages to help you "Megan" <Megan@discussions.microsoft.com> wrote in message news:329336A6-21D7-446D-ABBF-7D56FCECD6E6@microsoft.com... > I have checked the 'Page Properties - Advanced' - The Hyperlink Rollover > Effect has been checked on ALL pages. It only works on some of the pages. > I > am ...

mouse over button
Hi, I would like to ask what is the best way to found out, if a mouse is on a button, without clicking on a button. I just need to know when the mouse cursor is dragged over the button, than I will change its style. I was checking the cursor position in the WM-MouseMove handler of the dialog, but if the cursor is on the button, this message is not sent to the dialog. CButton doesn't have any similar WM_message, I could use. Thanks, Lubomir A button is essentially a window. So in other words any message sent to a Window or Dialog is sent to the button also. What I am trying to say...

Slides advance on mouse over
Hello There, I have a ppt presentation that I got from someone else. The use for this ppt is as a kiosk slide show. All the slides are set for advance on mouse click only. The problem is: If I select a slide from the main menu (the first slide in the ppt). it navigate to the slide indicated by the hyperlink on the main menu. This is good. But.... If the slide that I am viewing has buttons (aka hyperlinks) if I leave the mouse over the hyperlink, or mouse over the hyperlinks on the slide (most any slide in this ppt), after a short while, the slideshow jumps back to the main men...

Mouse over action in Powerpoint should allow floating text option
The "Action Settings" option in Powerpoint does not allow a floating text to be specified on "Mouse Over" or "Mouse Click" I have had to write macros to invoke external programs to generate a "Floating text" when I move the mouse over components I need to describe in detail during presentations. I frequently use Powerpoint 2000 to present Telecom architecture and the current features only allows a separate text box to be used to describe presentation components which becomes extremely laborious when assigning the custom animation for several comp...

mouse over display text
Hi, I have a continuous form that displays dealer codes in one column, I would like to provide the user with a function which displays further details (eg dealer name) about that dealer if they hover over a particular dealercode (based on country too). I have created a query which displays the correct dealer and know how to use dlookup to activate this, but i think this might only work when the record has focus as opposed to a mouse over. I tried using Stephan Lebans example for continuous forms, but because I want the dealer name to appear in the controltip text rather than in a...

Images in CListCtrl flicker on mouse over
Hi, I have a CListCtrl in "report" view and I have given it an Image List (CImageList). It only has 2 columns, with a variable number of rows. The Images are inserted ok however they flicker whenever the mouse is rolled over them. I read somewhere that this is because of "hot-tracking", so I tried to disable it by m_List.ModifyStyleEx(LVS_EX_TRACKSELECT,0); I also read that I should override OnLvnHotTrackSelectionList() but I tried that, and the function is still called even with the modified style as above, here is the fumction. void ImageSelectDlg::OnLvnHotTrackSelec...

Mouse over effect, anyone know how to do it?
I have a line chart with a number of series. You know how if you mouse over a line a tool tip shows showing the value, that tells me that excel can detect the mouse over event over a line. Is it then possible to use this capability to change the colour of the line whilt the mouse is over it? The effect I am after is a glow when the mouse is over a particular line on the chart. Any help would be much appreciated as it will be a cool effect. Kind regards, Mark Stephens Mark - Read my article on Chart Events: http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jo...

Can I mouse over an object and make it move?
I'm going to have a survey question on a slide where the answer is a number. I'm going to have small icons with the numbers on them (0, 1, 2, 3, 4, 5+). I only want the icon with the number 3 on it to be "clickable" and send them to the next slide. The other icons I want to move to another spot on the page if they mouse over the icon. Is this possible? How do I do this? Thanks in advance for any help! In article <AA0384FC-8446-44EC-8509-C71324B4F2A1@microsoft.com>, Sharpie wrote: > I'm going to have a survey question on a slide where the answer is a ...

Mouse Over
How do I insert a text box that pops up when I scroll over a picture or link? ...

Mouse Over Color
I really like how the close and minimize buttons glow and change colors in Vista when you point to them. Is there a way I can make my form command buttons in Access 2007 do that? Thanks -- Scafidel Lafayette, Louisiana Using the MouseOver event, you have some ability to induce button changes. There is an example at: http://www.datastrat.com/Download/MouseMove.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Scafidel" <jerryscafidel@bellsouth.net> wrote in message news:59772C78-2A0E-45EE-BD64-AB410B4B9BAF@micros...

CMenu: Prevent mouse over from opening sub menus
Can anyone point me to information on how disable submenus from opening when the mouse is paused over them? I would like to duplicate the feature that is found on the XP start menu "Open submenus when I pause on them with my mouse" but my searching so far has proved fruitless. Thanks, Greg ...

Excel Charts when I mouse over a certain cell...
i have an excel chart, a scatter diagram, which is working perfectly fine as it is. I just want to know if there is any way in which I can put this chart in a comment or click a button which will display the chart and ensure that the chart gets updated as the input values change??? Is this possible??? I tried the solution which Ed had suggested but the thing is I have 15 charts to display and hide on the mouse over event and am now struggling again with the same..Any cell I click gives me an error message.. I believe that it would chart the chart on its own based on the range selected, ...

mouse over hyperlinks
On one of my web pages http://www.robpendleton.co.uk/Links_page.htm as the mouse is hovered over a hyperlink the link text shrinks, which I don't like, and I can't find how to stop it. Any help would be appreciated. Here's the culprit <style fprolloverstyle>A:hover {color: #0000FF; text-decoration: underline; font-family: Trebuchet MS; font-size: 10px; font-weight: bold}</style> As a minimum remove font-size: 10px; You may think that whole line shouldn't be there - it's setting hover links to blue/underlined which they already are and changing...

Can you mouse over with Visio and display additional info?
I am developing an organiztional chart and I want to add some additional information to each shape but I don't I only want it to show when you click on the shape, or mouse over the shape is this possible? You can go to Insert > Shape ScreenTip to do this. -- Mark Nelson Microsoft Corporation This posting is provided "AS IS" with no warranties, and confers no rights. "CAC1911" <CAC1911@discussions.microsoft.com> wrote in message news:54ECC643-BBFE-433F-AB22-01F0BAD36282@microsoft.com... >I am developing an organiztional chart and I want to add some ad...