Solved

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

Posted on 2011-03-18
4
390 Views
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
        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
0
Comment
Question by:dlmille
  • 3
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35168598
Since it's a range being returned, it would just be:
 .XValues.Worksheet.Name

Open in new window

I think.
0
 
LVL 85

Assisted Solution

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

Author Comment

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

Thanks for the help!

Dave
0
 
LVL 85

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

839 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