Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Locate chart data for a selected point (xl2K sr9)

    I have an embedded XY scatter chart of a large amount of data. I would like to be able to select a point on the chart and then run a macro to select the data associated with just this point. If I could just figure out the XValue, I could do the rest. Something like Selection.Parent.XValues(???), but how do I know what to use for the index?

    In addition, I would really like to do this with a right-click on the point, but the BeforRightClick event only fires for ranges, not charts or points. This is probably not worth the effort. <img src=/S/yum.gif border=0 alt=yum width=15 height=15> TIA --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate chart data for a selected point (xl2K sr9)

    I think you can do this using the chart events. In the code module, behind each chart sheet, you can find the different events (this is not true for an embedded chart). The BeforeDoubleClick event has several arguments which must allow you to do what you want. e.g. the ElementID parameter returns an identifying number to indicate the element that was double clicked. If the chart element is a series (ElementID = xlSeries), Arg1 contains the series index in the SeriesCollection and Arg2 contains the the point index if a single point in the series has been selected. Arg2 is -1 if the whole series is selected.
    In case of an embedded chart, you can use events if you create a class module and go through a few other steps. But I don't know if this is what you want.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locate chart data for a selected point (xl2K sr9)

    Oh yeah! That is totally cool! <img src=/S/cool.gif border=0 alt=cool width=15 height=15> I'll start with a chart sheet and make the right-click select the data point and switch the chart to embedded right beside it. Thanks!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locate chart data for a selected point (xl2K sr9)

    Well, maybe not so cool: you have to use a double-click event and if you change a chart sheet into an embedded chart, you kiss your events good-bye! But I can work around that by making a copy of the chart sheet before I make it embedded.

    But, NEw QUestion -- I cannot see an easy way to set range objects equal to the original X and Y data ranges of a chart. I can get the data values easily with the XValues and Values property, but I don't see a path back to the original data except to parse the Formula property and that looks ugly. <img src=/S/help.gif border=0 alt=help width=23 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate chart data for a selected point (xl2K sr9)

    **** Geoff W- Long line in "Pre" tags split ****

    Sam,

    It is possible to retrieve the range address from the series formula and you can make events working for your embedded charts.

    In a module you should put the following code (by the way, I used ListSep="," but this can be different in your Windows environment; it depends on your regional settings):

    <pre>Option Explicit
    Public MyCh As New ClsChart

    Sub ActivateChartInstance()
    Set MyCh.MyChartClass = ActiveSheet.ChartObjects(1).Chart
    End Sub

    Function RetrieveRangeAddress(Ch As Chart, sSeries As Integer, _
    XorY As String) As Range
    Dim Ser As String
    Dim ListSep As String * 1
    Dim CommaCnt As Integer
    Dim Comma() As Integer
    Dim Temp As String
    Dim i As Integer
    Set RetrieveRangeAddress = Nothing
    On Error Resume Next
    Ser = Ch.SeriesCollection(sSeries).Formula
    ListSep = ","
    For i = 1 To Len(Ser)
    If Mid(Ser, i, 1) = ListSep Then
    CommaCnt = CommaCnt + 1
    ReDim Preserve Comma(CommaCnt)
    Comma(CommaCnt) = i
    End If
    Next i
    If CommaCnt > 3 Then Exit Function
    Select Case UCase(XorY)
    Case "XVALUES"
    Temp = Mid(Ser, Comma(1) + 1, Comma(2) - Comma(1) - 1)
    Set RetrieveRangeAddress = Range(Temp)
    Case "VALUES"
    Temp = Mid(Ser, Comma(2) + 1, Comma(3) - Comma(2) - 1)
    Set RetrieveRangeAddress = Range(Temp)
    End Select
    End Function

    Sub testChart()
    Dim MyChart As Chart
    Dim R As Range
    Set MyChart = ActiveSheet.ChartObjects(1).Chart
    Set R = RetrieveRangeAddress(MyChart, 1, "VALUES")
    MsgBox R.Address
    End Sub
    </pre>


    The testChart procedure is used as an example how to make use of the RetrieveRangeAddress function. It retrieves the range address of the XVALUES in the embedded chart on your spreadsheet (here I assumed that you started with a new spreadsheet with only one embedded chart on it).

    Secondly, if you want chart events to work for embedded charts, you have to make use of class modules. Insert a new class module and name it ClsChart (in my example, I did so, and the name is used as such further on in the code). Then add the following line to the class module:

    Public WithEvents MyChartClass As Chart

    If you now click the top left dropdown box in the class module code window, you will see that the MyChartClass object is now available and that you can select many events from the top right dropdown box. I added the following code to the beforedoubleclick event of the MyChartClass object:

    <pre>Private Sub MyChartClass_BeforeDoubleClick(ByVal ElementID As Long, _
    ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
    Dim Ser As Series
    If ElementID = xlSeries Then
    Set Ser = ActiveChart.SeriesCollection(Arg1)
    MsgBox "You selected point " & Arg2 & " from series " & Arg1
    End If
    End Sub
    </pre>


    As you probably noticed, I also added some code concerning the class events to the general module. This is because you have to instantiate your new object. Therefore I added the commandline

    Public MyCh As New ClsChart

    and added the ActiveChartInstance procedure.

    Now, if you make an XY scatterplot, with e.g. one dataseries with at least 5 points (this is because of my example code), then if you run the ActiveChartInstance macro, the events should be ready. Doubleclicking one point on the dataseries should make a messagebox pop up saying that you selected point x from series y.

    Hope this helps you achieving your goals.

Posting Permissions

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