Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart - Variable Series Collection
Hi,
I have a set of data that is linked to a chart. The range occupied by the data is variabe and I have code that find the new range and updates the Series Collection of the Chart. The code for setting the series collection is similar to this in that the chart is activated before being changed. Sheet1.Chartobjects("Chart 1).activate ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" This works, but the problem is that the workbook needs to be shared and thus the charts cannot be activated. Does anyone know how to refer to the SeriesCollection values without activating the chart first? Many thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart - Variable Series Collection
Only rarely is it necessary to Select or Activate anything,
With Sheet1.ChartObjects("Chart 1").Chart .SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" ' other stuff, eg .SeriesCollection(1).XValues = End With Maybe you could use a Dynamic Named Range instead, no code Regards, Peter T "Libby" wrote in message ... Hi, I have a set of data that is linked to a chart. The range occupied by the data is variabe and I have code that find the new range and updates the Series Collection of the Chart. The code for setting the series collection is similar to this in that the chart is activated before being changed. Sheet1.Chartobjects("Chart 1).activate ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" This works, but the problem is that the workbook needs to be shared and thus the charts cannot be activated. Does anyone know how to refer to the SeriesCollection values without activating the chart first? Many thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart - Variable Series Collection
Thanks Peter,
I knew there must be a way. I'm interested in being able to do this without code, but don't know much about dyamic ranges. Basically the range that the chart is created from has a set number of columns each representing a project and the rows represent the people. These people then enter the hours spent in the appropriate project. However, not all the projects are wanted in the chart so I've used a cell above each column header to indicate whether it's included. Potentially any combination of columns can be included and I haven't found a way of non-programmatically finding out which columns to include. "Peter T" wrote: Only rarely is it necessary to Select or Activate anything, With Sheet1.ChartObjects("Chart 1").Chart .SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" ' other stuff, eg .SeriesCollection(1).XValues = End With Maybe you could use a Dynamic Named Range instead, no code Regards, Peter T "Libby" wrote in message ... Hi, I have a set of data that is linked to a chart. The range occupied by the data is variabe and I have code that find the new range and updates the Series Collection of the Chart. The code for setting the series collection is similar to this in that the chart is activated before being changed. Sheet1.Chartobjects("Chart 1).activate ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" This works, but the problem is that the workbook needs to be shared and thus the charts cannot be activated. Does anyone know how to refer to the SeriesCollection values without activating the chart first? Many thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart - Variable Series Collection
When I suggested Dynamic Named Ranges I had in mind the usual way to adjust
the amount of data that will be displayed in a given series, eg to cater for new data added in new rows or to 'zoom' into a portion of the data. There must be hundreds of threads in the ng's about how to create such a Name, but providing you can use the Offset function that's basically all you need to remember, oh and include the name in a series formula like this - "myFile.xls!namedRange" to replace the cell reference. However, for your needs of adding new series and data (& removing?), Names are not going to help in the same way. If you've already got the logic of how to programmatically add (& Delete) series, and perhaps change the plotorder, you are probably just as well to stick with what you've got. Alternatively and if it fits overall, maybe you can plot all columns but data in the "don't plot" series would be Nulls. You'd need helper columns with formula something like this =IF(cell_plot_this_series=TRUE,data_cell,NA()) and plot the formula cells rather than the original data cells. Regards, Peter T "Libby" wrote in message ... Thanks Peter, I knew there must be a way. I'm interested in being able to do this without code, but don't know much about dyamic ranges. Basically the range that the chart is created from has a set number of columns each representing a project and the rows represent the people. These people then enter the hours spent in the appropriate project. However, not all the projects are wanted in the chart so I've used a cell above each column header to indicate whether it's included. Potentially any combination of columns can be included and I haven't found a way of non-programmatically finding out which columns to include. "Peter T" wrote: Only rarely is it necessary to Select or Activate anything, With Sheet1.ChartObjects("Chart 1").Chart .SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" ' other stuff, eg .SeriesCollection(1).XValues = End With Maybe you could use a Dynamic Named Range instead, no code Regards, Peter T "Libby" wrote in message ... Hi, I have a set of data that is linked to a chart. The range occupied by the data is variabe and I have code that find the new range and updates the Series Collection of the Chart. The code for setting the series collection is similar to this in that the chart is activated before being changed. Sheet1.Chartobjects("Chart 1).activate ActiveChart.SeriesCollection(1).Values = "=Sheet1!R3C2:R11C2" This works, but the problem is that the workbook needs to be shared and thus the charts cannot be activated. Does anyone know how to refer to the SeriesCollection values without activating the chart first? Many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable series multi line chart? | Charts and Charting in Excel | |||
Graph series collection | Excel Programming | |||
iterate through chart series collection | Excel Programming | |||
Series collection pb on Ecxel 2003 chart | Excel Programming | |||
Naming a worksheet as a variable in a Active Chart Series | Excel Programming |