Solved

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

Posted on 2011-03-18
4
381 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
Comment Utility
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
Comment Utility
Or .Xvalues.Parent.Name if you prefer.
0
 
LVL 41

Author Comment

by:dlmille
Comment Utility
duh!  I should have figured that out, lol.

Thanks for the help!

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
No worries. :)
Rory
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now