|
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 .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 |
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 .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 |
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.Del ete 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. |
| All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com