Returning SeriesCollection XValues individually (VBA)

[thesnowplow]

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

 

[thesnowplow]

Help?

 

[Worf]

• We could split that string using spaces as delimiters, but this method fails for “North Carolina”.
• Considering that the pivot chart is generated based on the pivot table, the field names can be obtained there.
• Please run the code below for the pivot table and confirm that the fields you want are data fields.

Sub Snow()
Dim p As PivotTable, pag$, col$, rw$, dt$, i%
pag = "": col = "": rw = "": dt = ""
Set p = ActiveSheet.PivotTables(1)
For i = 1 To p.PageFields.Count
    pag = pag & " / " & p.PageFields(i).Name
Next
For i = 1 To p.ColumnFields.Count
    col = col & " / " & p.ColumnFields(i).Name
Next
For i = 1 To p.RowFields.Count
    rw = rw & " / " & p.RowFields(i).Name
Next
For i = 1 To p.DataFields.Count
    dt = dt & " / " & p.DataFields(i).Name
Next
MsgBox "# Pivot fields: " & p.PivotFields.Count & vbLf & "Page fields: " & pag & vbLf & _
"Column fields: " & col & vbLf & "Row fields: " & rw & vbLf & "Data fields: " & dt
End Sub

 

[thesnowplow]

https://imgur.com/RveMbbs

See attached. Row fields are the ones that need separating.

 

[Worf]

On my sample pivot chart, the last string item is from a data field, so you should tweak the code where needed.

Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift&, ByVal x&, ByVal y&)
Dim ElementID As Long, Arg1 As Long, Arg2&, i%, myX, p As PivotTable
Set p = Sheets("PivotSheet").PivotTables(1)
With ActiveChart
    ' Pass x & y, return ElementID and Args
    .GetChartElement x, y, ElementID, Arg1, Arg2
    If ElementID = xlSeries And Arg2 > 0 Then
        myX = WorksheetFunction.index(.SeriesCollection(Arg1).XValues, Arg2)
        For i = 1 To p.DataFields.Count                 ' inspect data fields
            If InStr(1, myX, p.DataFields(i).Name) Then
                MsgBox p.DataFields(i).Name             ' appears in myX
                Exit For
            End If
        Next
    End If
End With
End Sub