Getting the XValues Range from a Chart - how to obtain the ActiveSheet qualifier for scope?

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
   
    With MySeries
        .Chart = myCht
        .ChartSeries = 1
        If .XValuesType = "Range" Then
            getXValuesRange = .XValues.Address
            'MsgBox .XValues.Address
        Else
            'do nothing
            'MsgBox .XValues
        End If
    End With
   
End Function


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?

Cheers,

Dave
LVL 42
dlmilleAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Since it's a range being returned, it would just be:
 .XValues.Worksheet.Name

Open in new window

I think.
0
 
Rory ArchibaldConnect With a Mentor Commented:
Or .Xvalues.Parent.Name if you prefer.
0
 
dlmilleAuthor Commented:
duh!  I should have figured that out, lol.

Thanks for the help!

Dave
0
 
Rory ArchibaldCommented:
No worries. :)
Rory
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.