Unanswered: Get value of selected x axis using VBA
I'm attempting to build a 'drill down' in a pivot chart using VBA in Access 2010. The User will click on the particular segment of a stacked bar representing a ChannelName. ProductCategory is used as the horizontal X axis with a numerical value as the vertical Y axis. I need both the ChannelName and ProductCategory in the user's selection to be able to change the form source for the next view.
So far after trawling through many posts and taking/testing parts of code across them, I've managed so far to get the value of the stacked column that is selected. I am at a loss so far on retrieval of the selected ProductCategory on the X axis.
Is anyone aware of how to do this or of a site that identifies the various Chart properties and methods?
Many thanks for any help.
Dim SelectedObjectType As Integer
Dim SelectedBar As String
Dim SelectedX As String
SelectedObjectType = Chartspace.SelectionType
SelectedBar = Chartspace.Selection.Parent.Name
'Message box to test value returned
MsgBox "Selected Bar: " & SelectedBar
SelectedX = Chartspace.Selection.Who.What 'Properties needed here. Presumed chartspace.selection is still used??
'If Bar is selected
If SelectedObjectType = 8 Then
'Swap form source etc, etc...