Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm stumped in using the GetChartElement in Excel 2000/VBA.
    I'm not at all certain what I want to do is possible.
    The attached workbook has code cloned from Jon Peltier's excellent article "Chart Events in Microsoft Excel" at http://www.computorcompanion.com/LPMArticle.asp?ID=221

    Clicking on a data point returns the information I need - when I click on a data point.

    I want to click on an data-less area of the chart - an axis for example - and know which axis I'm on and where abouts I am on that axis.

    In the attached JPG I have indicated with a red arrow that I'd like to be able to click on the "B" category radial and be told not only that I am ON the B radial, but as well that I am at or near the 135,000 value mark.

    The GetChartElement for an xlAxis seems to return little else but the AxisIndxe and AxisType (duly reported by the VBA code), but appears not to be capable of returning anything that indicates position, and hence value along that axis.

    Am I barking up the wrong tree (or even just plain barking mad?)
    Attached Images Attached Images
    • File Type: jpg 4.JPG (100.1 KB, 4 views)
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='chrisgreaves' post='799259' date='21-Oct-2009 22:42'](or even just plain barking mad?)[/quote]
    Er, well, anyway...

    You'd have to calculate the value along the axis from the X and Y coordinates that are passed to the Chart_MouseUp event, but I think that'd be a daunting task, if it is at all possible.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799261' date='21-Oct-2009 16:58']You'd have to calculate the value along the axis[/quote]
    Woof! (rough translation: Thanks.)

    If I am on the right track, getting the position on the axis would be OK.
    If I know the category (in my screen shot, which of the lettered radials A-M is being selected) and the position along that line Then I can use the maximum value of the value series to calculate the equivalent value.

    In my screenshot, If I am told that I clicked on the "B" radial where the arrow is, and if that were reported as 90% of the way along the line, I could determine
    that 90%x150,000 is 135,000, and that the user had hoped for something in the 135,000 range on the B category.

    If I were told some (x,y) coordinate within the plot area, a bit of trigonometry should tell me the same thing, I think.
    My plot area is 480x480 pixels
    The arrow is at (341,45), so (pulls out tables of sines, cosines etc) so the user had hoped for something in the 135,000 range on the B category.

    So now my question reduces to:
    Does anyone offer any clues as to how to get X and Y coordinates that are passed to the Chart_MouseUp?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think you'd have to calibrate your calculations by having a macro ask the user to click (as accurately as possible) on three "known" points in the chart. You'll get the X and Y coordinates of those points, and you could those to get your bearings.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You'll get the X and Y coordinates of those points, and you could those to get your bearings.
    I'm making progress.
    This page http://www.computorcompanion.com/LPMArticle.asp?ID=221 by Jon Peltier is an excellent source of sample code.
    Jon demonstrates that the Chart_MouseUp event returns not only information about a specific element, but also the (x,y) coordinate of the mouse on the screen.
    Further, "Shift tells which keys are depressed when the mouse button was released.", hence we have 8 combinations of { none | shift | ctrl | alt }

    In this manner the user can communicate eight different actions to be taken when a data point or legend item is clicked.
    Since the legend items refer to an entire data series, this suggests that we can use 8 combinations of click on the legend for actions that take place on an entire data series, and 8 combinations of click on the data point for actions which take place on a single data point within a data series.
    Since the user can click on a legend entry, and by a second, delayed click, select a legend key, this suggests 16 distinct actions on a data series as a whole and 8 distinct actions on any data point within any data series.

    That's a lot of analytic power on a chart.
    I think I'll go lie down for a little while.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by chrisgreaves View Post
    I'm making progress.
    One can implement some cute user facilities with the Mouse-Up event:
    Code:
        Case xlChartArea
    '        MsgBox "xlChartArea" & " Shift=" & Shift & " x=" & x & " y=" & y
            Dim lngHeight As Long
            lngHeight = ActiveChart.ChartArea.Height
            Dim lngWidth As Long
            lngWidth = ActiveChart.ChartArea.Width
            Dim lngQuadrant As Long
            If x < (lngWidth / 2) Then
                If y < (lngHeight / 2) Then
                    lngQuadrant = 0
                Else
                    lngQuadrant = 3
                End If
            Else
                If y < (lngHeight / 2) Then
                    lngQuadrant = 1
                Else
                    lngQuadrant = 2
                End If
            End If
            Select Case lngQuadrant
                Case 0
                    MsgBox "top left"
                Case 1
                    MsgBox "top right"
                Case 2
                    MsgBox "bottom right"
                Case 3
                    MsgBox "bottom left"
            End Select
    The verbose code demonstrates that we can detect which quadrant of a chart area is clicked.
    Supposing a radar/spider chart; the corner areas of the chart are devoid of stuff. Clicking in a corner can indicate that one of four actions is to be taken.
    Of course we need not restrict ourselves to 4 quadrants; we could break the chart area into {foot-formica alert}octants{/foot-formica alert}.
    But better yet, we could use the chart area as a slide bar, so that a click on the chart area across the top of the chart, 37% of the way in from the left could signal that the font size of the title text is to be reduced to 37% of its current size. A click on the chart area down the right-hand side of the chart, 68% of the way down from the top could signal that the RGB values of each marker point are to be increased by 68% of their current values. A click on the chart area down the LEFT-hand side of the chart, 32% of the way down from the top could signal that the RGB values of each marker point are to be DECREASED by 32% of their current values, and so on.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As you show, you can use the MouseUp event to perform different actions depending on where on a chart the user clicks.

    But for the particular example that you mention, I'd prefer to use scrollbar controls (from the Forms toolbar or the Control Toolbox toolbar) - they provide the user more visual feedback.


  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    ... I'd prefer to use scrollbar controls ...
    Yabbut!, (he retorted, like a good Canadian ) in this case the client is using the Excel chart as a presentation, and wants to avoid, as much as possible, any extra controls on the screen.
    Being able to devise "controls" known only to the presenter is a Good Thing.
    Apart from which, I hate seeing a click go to waste!
    I anticipate a raft of request for extra control once the initial chart is rolled out and the initial presentation takes place.

    More than anything I'd like to let others know of the awesome power behind trapping events in Excel 2000 alone.
    Again, This page by Jon Peltier is an excellent source of sample code.

Posting Permissions

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