+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Randall Hiltz
    Guest

    [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?

  2. #2
    Jon Peltier
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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?



  3. #3
    krhiltz@uniongas.com
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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


  4. #4
    Tushar Mehta
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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
    >
    >


  5. #5
    krhiltz@uniongas.com
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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


  6. #6
    Tushar Mehta
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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 <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
    >
    >


  7. #7
    Stephen Bullen
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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



  8. #8
    Tushar Mehta
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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
    >
    >
    >


  9. #9
    Stephen Bullen
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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



  10. #10
    krhiltz@uniongas.com
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    Thanks. Hiding is worth a try.


  11. #11
    Jon Peltier
    Guest

    Re: Use the "mouse over" event to exclude a data point from a series

    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.
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1