Dear all,

Is there a way to create a list of all dataseries of a chart and output
their names to excel? I want to create a list of checkboxes dynamically for
a chart based on user selection...

Regarding pivot table, can I remove all formula/data from a pivot table by
code? For example, I want to add some metric to a pivot table based on
selection on a userform..first, I got to remove all existing data/formula
from the table. Also, do you know is it possible to change a pivot table's
name programmingly?

Thank you for your posting!

x"rookievestor" wrote:
Is there a way to create a list of all dataseries of a chart and output
their names to excel? I want to create a list of checkboxes dynamically for
a chart based on user selection...

What exactly do you want to do here? Draw a chart based on the user's
selection of which series should be plotted? In that case, you would want to
make a list of possible ranges to include in the chart, present the options
to the user, then draw the chart based on the response.

If you start with names of series from an existing chart, you can only
remove items from that list. That is, there's no "Visible" property that I
know of that you can use to hide a series, so you can't just turn one on or
off. Once you delete a series, it won't be there the next time you want to
present options to the user. Is that what you want?

Maybe I misunderstand what you're trying to do. In any case, here's
something to get you started down the chart-to-series path:

Dim chartSeries as Variant
...
Worksheets("Sheet1").ChartObjects("Chart 1").Activate
For Each chartSeries In ActiveChart.SeriesCollection
Debug.Print chartSeries.Name
Next

You can access the SeriesCollection this way, too:

Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection

The trick there is to recognize that a ChartObject contains a Chart. It
isn't the Chart itself.

To get started with code going in the other direction, try recording macros
while you make charts with different data series included.