Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-03-18
4
Medium Priority
?
407 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 2000 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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

581 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