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

Posted on 2011-03-18
Medium Priority
Last Modified: 2012-05-11
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
            '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?


Question by:dlmille
  • 3
LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 35168598
Since it's a range being returned, it would just be:

Open in new window

I think.
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35168604
Or .Xvalues.Parent.Name if you prefer.
LVL 42

Author Comment

ID: 35168897
duh!  I should have figured that out, lol.

Thanks for the help!

LVL 85

Expert Comment

by:Rory Archibald
ID: 35168915
No worries. :)

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

Join & Write a Comment

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question