Pivot Chart selected points stored as cell values

[edgarsrom]

Hello all, I am struggling to put a VBA code together for Chart_Select event. My aim is to get x2 values based on user selection on the pivot chart and allocate them into an empty cells, eg. into "Data" sheet tab cell A1 first selection/A2 cell for second selection (probably using IF statement to check if 1st selection is already made). I then want to compare percentage change between two selected points on the chart and display them for example in MsgBox.

End goal, for example:
1st Selection on pivot chart:
Cell A1 saved value = 400

2nd Selection on pivot chart:
Cell A2 saved value = 1000

% change: 1-(400/1000) = 60%

After some research online I came across VBA code for Chart_Select event which captures X Axis value based on selection:

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Select Case ElementID
    Case xlSeries
        If Arg2 > 0 Then
            x = ActiveChart.SeriesCollection(Arg1).XValues
            For i = LBound(x) To UBound(x)
                If i = Arg2 Then
                    MsgBox "Point :" & i & "and X Value = " & x(i)
                    Exit For
                End If
            Next i
       End If
    End Select
End Sub

It kind of does what I would like, but captures SeriesCollection instead of Point. Then I found another VBA code which does what I want, but not using Chart_Select event:

Sub PointSelection()
    If TypeOf Selection Is Point Then
        Dim p As Point
        Set p = Selection

        Dim i As Long
        i = CLng(Split(p.Name, "P")(1))

        Dim s As Series
        Set s = p.Parent

        Sheets("Data").Range("a1").Value = s.XValues(i) '- Horizontal axis eg. date
        Sheets("Data").Range("b1").Value = s.Values(i) '-Point value
    End If
End Sub

Is anyone able to combine them into 1 vba code please?

Any help much appreciated!

 

[edgarsrom]

Update on my above thread: I am nearly there, but it is still not working. For some reason it returns same values for both selections regardless where I press on the pivot chart. Updated VBA below:

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Select Case ElementID
    Case xlSeries
        If Arg2 > 0 Then
            x = ActiveChart.SeriesCollection(Arg1).XValues
            p = ActiveChart.SeriesCollection(Arg1).Values
            For i = LBound(x) To UBound(x)
                If i = Arg2 Then
                    If Sheets("Data").Range("a1").Value = "" Then
                        Sheets("Data").Range("a1").Value = x
                            Else: Sheets("Data").Range("a2").Value = x
                    End If

                    If Sheets("Data").Range("b1").Value = "" Then
                        Sheets("Data").Range("b1").Value = p
                            Else: Sheets("Data").Range("b2").Value = p
                    End If

                    Exit For
                End If
            Next i
       End If
    End Select
End Sub