Selecting (clicking on) a line graph (Excel) and having the specific coordinate stored in a cell or a variable that can be used for a VBA code

Question

I would like it for the user to be able to select a linear section of this graph:

So that we can obtain a reduced section, like this one:

This with the aim of performing additional calculations only on the values or data points corresponding to the reduced section.

Answer 1

@Jose Gabriel, I was going to wait until you responded to my question in the comments, but I believe the following will get you the pieces you need and you can simply customize them to your needs.

The basic process you are asking about involves Three functions.

First, to report the location on the chart that the users clicks and the corresonding point of the series.

(I would suggest a double-click actually, as a single click is generally used for selection and double-click is used for actions).

Second, to find the selected point within the seriescollection values to create the two (or more) points necessary to create your reduced chart.

Third, to modify the source data for the presentation of the chart.

Step One - Report location within the chart series

Below is a link to a comprehensive, yet absolute beginner walk through of identifying which point/segment of a chart was selected by the user.

It includes much more than that, but might also be helpful to give you enough of an understanding of what is available to you that it should all be useful.

http://peltiertech.com/chart-events-microsoft-excel/

The direct segment you are looking for on a single line series is about a third of the way down the page. Here is an excerpt with only the code applicable to your question:

Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)
 
    Dim sElement As String
    Dim sArg As String
 
    Select Case ElementID
        '... several other case options

        Case xlSeries
            sElement = "Series " & Arg1
            If Arg2 > 0 Then sArg = "Point " & Arg2

        '... several other case options
        End Select
 
     MsgBox sElement & IIf(Len(sArg) > 0, vbCrLf & sArg, "")
End Sub

(note: just below that is a section to discover the coordinates x & y)

So, now you have the segment that the user selected in a series. The point selected is always the higher of the two points of the segment.

Step Two - discover where the selected point falls in the series collection

In order to build the reduced chart, you must identify the selected point within the seriescollection values and then select the point just before that one to create the reduced chart.

Here is a link (and solution code below link) to a similar question, although it will loop through all series of all charts and sets the last value. You can limit this by identifying just your chart(s)/Series if needed.

Also, you don't need to add the last value, instead, I suggest you store the previous point in a variable and check to see if the current point is the point the user selected. If so, you have your segment and can reset your chart.

VBA looping through all series within all charts

(Remember, you will need to add a loop to iterate through each Point in the series Points)

Step Three - Modify chart series data

Now, all there is left is to reset the chart to the new points identified from step 2

Here is a link, but you may not even need it. https://msdn.microsoft.com/en-us/library/office/ff197014.aspx

Here is the code example used to demonstrate assigning constant values:

Charts("Chart1").SeriesCollection(1).Values = Array(1, 3, 5, 7, 11, 13, 17, 19)

If you want the user to be able to go back and forth from the original to reduced charts, I suggest using a named range for the original chart and allowing the user to double click the chart (may a specific section of the chart) to revert back to the full data series.

Hope that does it for you.