Below code, I use in a class module to return XValues from a chart whenever a user clicks on any data point. However, if the PivotChart has multiple category X axis labels, it scoops them all up and concatenates using space. e.g. if PivotChart will have columns "Continent", "Country" and "State" on X Axis, myX will return "North America United States Texas". How do I split them into 3 separate variables something like myX1, myX2 and myX3? Or how do I extract just State from myX without all the rest of the stuff that comes along with myX currently?
Option Explicit Public WithEvents EventChart As Chart Private Sub EventChart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long) Dim ElementID As Long, Arg1 As Long, Arg2 As Long Dim myX As Variant With ActiveChart ' Pass x & y, return ElementID and Args .GetChartElement x, y, ElementID, Arg1, Arg2 ' Did we click over a data point? If ElementID = xlSeries And Arg2 > 0 Then ' Extract x value from array of x values myX = WorksheetFunction.Index _ (.SeriesCollection(Arg1).XValues, Arg2) ' return myX MsgBox "Test: " & myX End If End With End Sub