Chart: range info from seriescollection?

I would like to fill a combobox (Rowsource) with data that are used in for 
instance the XValues or Values of a Seriescollection of a graph.
"ActiveChart.SeriesCollection(1).Values" does not give the right type (I was 
hoping for a string type of info like "Sheet1.Range(a1:a10)").
How to get a Range to be used as Rowsource for my combobox???

Thanks for your suggestions,

JB
0
5/23/2006 9:48:02 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
768 Views

Similar Articles

[PageSpeed] 41

The Values and XValues will return an array of values. To get the range 
address like this, you will need to parse the series formula. Or you could 
use John Walkenbach's class module for extracting elements of the chart 
series:

  http://www.j-walk.com/ss/excel/tips/tip83.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Excelerate-nl" <Exceleratenl@discussions.microsoft.com> wrote in message 
news:AA34BBB1-BC22-4FCE-B14D-76C1F6BF9E97@microsoft.com...
>I would like to fill a combobox (Rowsource) with data that are used in for
> instance the XValues or Values of a Seriescollection of a graph.
> "ActiveChart.SeriesCollection(1).Values" does not give the right type (I 
> was
> hoping for a string type of info like "Sheet1.Range(a1:a10)").
> How to get a Range to be used as Rowsource for my combobox???
>
> Thanks for your suggestions,
>
> JB 


0
jonxlmvpNO (4558)
5/24/2006 3:37:53 AM
Reply:

Similar Artilces:

Query date range
I have a query asking for records whose field (CombinedDate) is not null. I'd like be able to pull out only records whose CombinedDate occurs within the next 2 weeks. I have trouble creating expressions for dates & don't really catch when to use DateDiff, DateAdd, or something on the order of Date()+. Appreciate your help. Field: CombinedDate Criteria: Between Date() and DateAdd("d",13,Date()) Use DateAdd when you want to create a date. Use DateDiff when you want to determine the difference in two dates Use Date() + when you want to add a specific number of days o...

Naming ranges as a copy of another sheet
Dear all, There are two sheets X and Y in my workbook. On A, there are hundreds of ranges named locally (i.e. names are like "X!students"). How to write a macro to name the respective areas in Y with the same local name? For example, if X!$A$1:$B$4 is named as "X!students", then I want Y!$A$1:$B$4 to be named as "Y!students". Thanks in advance. Best Regards, Andy Something like: Option Explicit Sub testme01() Dim wksMstr As Worksheet Dim wksOther As Worksheet Dim nm As Name Dim testRng As Range Dim ExclamPos As Long ...

Reference a cell in a named range
How do you reference a single cell in a named range? I have: A B C 1 13 =A1^2 =A$1^2 2 35 =A2^2 =A$2^2 3 51 =A3^2 =A$3^2 4 79 =A4^2 =A$4^2 Define the name "Alice" for column A Then the array is A B C 1 13 =Alice^2 =A$1^2 2 35 =Alice^2 =A$2^2 3 51 =Alice^2 =A$3^2 4 79 =Alice^2 =A$4^2 In a macro I need to use Alice, rather than A because I may insert columns before A. That's no problem, but I also need to refer to specific absolute rows in the column. I need column D to be: A B C ...

How to write a macro to modify an Excel chart
I want to "record" a macro that, for example, changes the line styles on an Excel chart. I tried by selecting the chart, then clicking on "record macro", manually changing the line style, and then clicking on "stop recording". But then when I tried to use the macro it wouldn't do anything. When I edited the macro there was no code, just the comment lines giving the shortcut. Is it possible to use the "record" feature for macros that work with charts, or must one start with the VB editor? Thanks, Henry -- leave well enough alone Hi, Here ...

How do I create a gnatt chart in Excell
I am trying to create a milestone chart with the timeline on the X axis. Gantt Charts in Microsoft Excel http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Candie" <Candie@discussions.microsoft.com> wrote in message news:A0E570B3-17C1-4F59-AF8F-F781262890F8@microsoft.com... >I am trying to create a milestone chart with the timeline on the X axis. ...

Plotting a datapoint on an existing Line chart
Hi all. I have a line chart, for the sake of simplicity, lets say th line chart depicts the garph of y = x. What I need to do now is displa a particular point on this line, lets says (2,2). How can I add thi point so that I lies on the line chart? Also, what I would like to d is have a horizontal line connecting the point to the y-axis and vertical line connecting to the x-axis. On top of that, the vertica line from the x-axis will have the corresponding x value of the poin (2) displayed and the vertical line will display the corresponding value (also 2). The additional complexity is that t...

Access chart
I created a chart but I need to show my total on the pie not just my legend. For example: In this pie shows the model number of the equipment, but it doesn't show me how many of them I purchased and that's what I need to show in addition to the model number. thanks It isn't clear what the pie chart is charting. You can set the labels to display a number of options. Did you "play" with this property? -- Duane Hookom Microsoft Access MVP "naja" wrote: > I created a chart but I need to show my total on the pie not just my legend. > > For exam...

functions in charts
Is it possible to enter a user-defined function (that returns an array) for values in a chart instead of specifying a range in the worksheet? Joe - You can specify a named range, with the formula built in. See the Dynamic Chart examples and links on my web site: http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html A good example of a formula in a named range can be found on Stephen Bullen's site: http://bmsltd.co.uk Look for the ChtFmla.xls example. Tushar mehta has a similar example on his site: http://tushar-mehta.com In VBA it's possible to assign an a...

2007
Hi! - I was hoping someone could help me please. Here's what I have. A worksheet that has several dates in a row. D1:T1, Of that range, I have conditional formatting set to highlight a date(s) either = to or within the next 7 days. I need to be able to pull the date next closest or equal to today. Can anyone help me out? Any help would be greatly appriecated. Shaun > I was hoping someone could help me please. Here's what I have. A > worksheet that has several dates in a row. D1:T1, Of that range, I > have conditional formatting set to highlight a date(s) either = to or...

How do I make a pie chart with 42 answers that shows % each out of
I want to graph survey responses where peolple could pick more than one answer. So there would be some overlap in the pie chart, is this possible? Please give us more details, -- Thanks, Shane Devenshire "brmg" wrote: > I want to graph survey responses where peolple could pick more than one > answer. So there would be some overlap in the pie chart, is this possible? ...

Creating Dynamic Range
I have created a dynamic range for an excel 2000 spreadsheet that I reference by using a Data Validation List... The dynamic range almost works, except that is leaves whichever is my last entry to the list off, of my "drop-down" box... So anything I add to the list whether is is several or a couple, will show up in my drop-down box except the very last entry.... My list that I am creating the dynamic range from runs from cell A3 thru A20 The formula for the dynamic range I have is: =OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A:$A)) Can anyone recognize something that might be wron...

Autoshapes within a chart
I want to place a transparent box behind the bars of a bar chart to denote a highlighted section of a timespan. I can get the transparent autoshape in the chart but I can't get it to move behind the bars. Can anybody help? Many thanks Renee Hi Renee, I don't think you will be able to do that. Instead have a look at Jon's example of quadrent area. You should be able to adapt this technique to give you a highlighted section. (http://www.geocities.com/jonpeltier/Excel/Charts/ColoredQuadrantBackground.html) Renee wrote: > I want to place a transparent box behind the bars of...

how to copy sheet with charts without link to original data
I have a worksheet with ten charts on it. I need to copy the worksheet with all its data and related charts to new sheets or files so I have a page for every day, with different data entered every day. But when I copy the worksheet with embedded charts and paste it into a new file, the charts pull their source data from the original worksheet, not the new one. The source data Value box is written as such, ='03-09-10'!$D$12 any ideas? Hi Esty, Probably wont help you, but I had similar problem, I posted few days ago. Didn't resolved it yet, but Jon suggested ...

Chart/Graph colours
My graph colours have all changed to a wishy washy mix of the same colour. I know updates happen on a regular basis, but how can I get back to selecting nice bold colours for my charts without having to revert to selecting each data series and recolouring them individually. Hi Try to reset the colour pallette Tools > Options > Select "Colour" Tab > Reset Regards Rajesh Mehmi rajesh@mehmi.net "redford01" <redford01@discussions.microsoft.com> wrote in message news:A782B3F1-D58C-4953-BEE0-9A66CC20FDD5@microsoft.com... > My graph colours have all cha...

Compile error on range...
I get a compile error on line 21 below. What is wrong with it? I am trying to color fill a cell based on the XColor value... Help! 0 r = 1 1 Do While ActiveSheet.Cells(r, 1) <> "" 2 str1 = ActiveSheet.Cells(r, 1) 3 str2 = ActiveSheet.Cells(r, 2) 4 comp1 = strip(str1) 5 comp2 = strip(str2) 6 7 If (comp1 <> comp2) Then 8 XColor = 5 9 Else 10 XColor = 25 11 End If 12 13 If (XColor) Then 14 Range("ActiveSheet.Cells(r, 1):ActiveSheet.Cells(r, 2)").Select 15 With Selection.Interior 16 .ColorIndex = 3 17 .Pattern = xlSolid...

Can't edit charts
For some reason, I can't edit any of the charts in an Excel 2007 file. I can't left-click or righ-click. Is the file "shared"? Are sheet grouped together? Have the sheets or charts been protected? Advanced Excel Conference - June 17-18 2009 - Charting and Programming http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Anawynn" <Anawynn@discussions.microsoft.com> wrote in message news:FAB71D11-1CE5-4175-B4FF-BD9292C187A3@microsof...

How Do I Chart This in Microsoft Access?
Hello, I have the following simple data: Company Monthly Sales Percentage OF Total Sales ------------- -------------------- ---------------------------------------- Bakery $50,000 0.50 Carpenter $25,000 0.25 Plumber $25,000 0.25 I would like to create some sort of a graph or chart in Microsoft Access that will show all three fields. When I try to create a pie chart or column chart it just gives me either the "company and t...

using the range method
i want to select certain columns and rows and use this selection to create a chart. The selection is in a loop and the cell locations will vary every time it is run. Here's my code: Worksheets(1).Range(Cells(startRow, 16), Cells(endRow, 18)).Select where "startRow" and "endRow" are integers. I always get the error that "Method 'Cells' of object '_Global' failed" Am I using the wrong format for the Range method? thanks in advance J.E. McGimpsey wrote: >or the much more efficient: > > With Worksheets(1) > ...

How to enlarge chart size for viewing?
I have some charts that I created on a sheet itself. However, the chart did not occupy the whole sheet instead only a small portion in the centre which is abit too small to view. How can I resize it? If you move the cursor over one of the square black blocks on a side, or in a corner, of the chart, the cursor will turn to a resizing arrow. -- David Biddulph "GOH" <GOH@discussions.microsoft.com> wrote in message news:154DCB76-95A3-4ADE-9AB9-AA8E3AAA3BD6@microsoft.com... >I have some charts that I created on a sheet itself. However, the chart did > not occupy the whol...

Efficient Chart Macro
I am using the below procedure (copy from Record Macro) to generate 1 graphs, each graph has 4 rows X 50 columns, by supplying ranges o data. In my opinion, the code is not very efficient because it take quite wait to plot and during the process of plotting a chart, it redraws th chart many times. Is there any codes that I can be omitted or rearranged to make it plo faster? Appreciate your help. Sub PlotProgressCurve(pSect As String, pRangeDate As String, pRangeN As String) Dim tRangeDate, tRangeNo As String Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.SetSourceData S...

Changing formatting on bar charts
I have a bar chart that shows turn around times for various jobs our group has in process. The chart shows the customer name @ the bottom, # of days up the side then the bars are for all the jobs each customer has. Here are my problems/things I want to change: 1) the spacing for each customer is equal whether they have 1 job or 10, can that be adjusted as needed? 2) My bars are so narrow that you can barely see the color because of the customer spacing issue mentioned above(I assume). How can I change the width of these bars? ...

charting a project overview
Is there a simple way to build a chart to show a overview of multiple projects including their milestones. For example I have Project A, B, C. Each project has milestones 1, 2, 3, 4, 5, 6 each with start and end dates. I would like to chart these projects where each project has a bar (or set of bars) and each milestone has a subbar (assuming no overlap). The x axis would be the dates. for example: My Projects: Project1 (show bar of milestone1) (bar of milestone2) (bar of milestone 3) .... project2 (show bar of milestone1) (bar of milestone2) (bar of milestone 3) .. project3 (s...

Plot R-Theta [angle (0~360 degrees) and amplitude] chart in Excel
How do you plot an R-Theta chart in Excel, i.e. a circular chart with the independent axis (angle) in degrees from 0 to 360 degrees and amplitude as the radial distance from the center point? In article <CE5D63AB-12A3-4042-928A-6D6C7CA8DBFE@microsoft.com>, Wes12 @discussions.microsoft.com says... > How do you plot an R-Theta chart in Excel, i.e. a circular chart with the > independent axis (angle) in degrees from 0 to 360 degrees and amplitude as > the radial distance from the center point? > You have to convert the data to (x,y) with the formulas x=r*cost(theta) and y...

Importing data from Access and creating statistics and a pie chart
I have an Access database field that contains the name of a part, which is one of 93 parts. I want to be able to import this data to Excel so that I can statistically show the numbers of each type of part sold within a specific time frame. For instance, we sold 25 widgets last quarter which is a 15% increase over the previous quarter. The imported data would be broken down by part name, and then converted to a pie chart. I think I know how to limit the time frame in Access, but am lost when it comes to importing the data and generating the statistics in Excel and creating the pie char...

pivot chart/table
Using the same source date, how can I create a pivot table and a pivot chart on the same worksheet See the post above -- Regards Frank Kabel Frankfurt, Germany pfc wrote: > Using the same source date, how can I create a pivot table > and a pivot chart on the same worksheet ...