Changing Displayed Data and Legends thru VBA

[BenWeber]

Are there any vba functions to change the data displayed in the data area of
the chart and/or legend text through VBA (ie not using the wizard, instead
need to do this in code)?

Specifically, I would like to put checkboxes on a worksheet to enable the
user to turn on-and-off data that's displayed in a chart (there's about 10
stats they could choose from, and it gets busy if all are shown).

Thanks!
-Ben

 

[Del Cotter]

Are there any vba functions to change the data displayed in the data area of
the chart and/or legend text through VBA (ie not using the wizard, instead
need to do this in code)?

Specifically, I would like to put checkboxes on a worksheet to enable the
user to turn on-and-off data that's displayed in a chart (there's about 10
stats they could choose from, and it gets busy if all are shown).

Pivot tables let you check and uncheck ranges. You may also be able to
use the controls available in the Form Design toolbar, if you don't want
to use a Pivot chart.

 

[Jon Peltier]

Pivot tables let you check and uncheck ranges. You may also be able to use
the controls available in the Form Design toolbar, if you don't want to
use a Pivot chart.

For example:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon

 

[BenWeber]

Both good answers.

Problem for me is that i was trying to simplify the question. I'm actually
using the chart outside of excel (it's actually in MS Access). That's why i
tried to specify using VBA, although i did use "checkboxes on a spreadsheet"
in the example because I thoguht it would be the same method so would be
simpler.

Are there functions that allow you to change the data being displayed via
code?

Ie methods to do what you'd do in the wizard if you took a value out of the
stats being displayed in the data area of the chart?

Thanks!
-B

 

[Jon Peltier]

In Excel you can use VBA to change the data used by individual series or by
the entire chart. I don't know how charts work in Access.

- Jon

 

[BenWeber]

I really wanted to avoid changing the data for optimization perspective.

I did a record macro and changed the included fields. Looks like the
property i need to change is:

ActiveChart.PivotLayout.PivotTable.PivotFields("Sum of Unix").Orientation = _
xlHidden

Thanks,
-B