I'm currently using this tip on an app that I've been working on: http://j-walk.com/ss/excel/tips/tip83.htm
I created a simple function around this class module that returns the string address of the active chart's XValues range:
Function getXValuesRange(myCht As Chart) As String
Dim MySeries As New ChartSeries
.Chart = myCht
.ChartSeries = 1
If .XValuesType = "Range" Then
getXValuesRange = .XValues.Address
This works swell for embedded charts, and I've built the activesheet.name ahead of that range so I can get the fully scoped name of the chart, which is important to follow-on logic. However, if I run my function against a Chart Sheet (where the chart is a tab in and of itself), prefixing with the Active Sheet name gives me the tab name of the chart, not the sheet name where the data is.
Would someone please assist by looking at the class module presented in the link, and let me know what I could add to the interrogation to return the fully scoped range of Xvalues?