By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,266 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,266 IT Pros & Developers. It's quick & easy.

Changing data field in a pivot chart via button (vba)

P: 374

Basically I am trying to change my pivot chart a bit more dynamically.

I have a sub form with a query recordsource which contains all the fields I would require to create TWO pivot charts.

The sub form opens as pivot chart view and ofcourse is placed as a control on my main form.

I am trying to design my form with some code so that when I click buttons on my main form the data categories on the axis of the pivot chart change to predefined (by the code) data series I have set.

This in my mind seems very simple since it is literally just asking my pivot chart to stop using a category for the Y axis such as [NCCost] to now picking out [NCEventCount] from its record source (query).

I have been looking for ages at code and it seems to always reference excel forums. Anyway.. this is what I have at the moment:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnsettocosts_Click()
  2. Dim objChartSpace As OWC10.ChartSpace
  3.    Dim objPivotChart As OWC10.ChChart
  4.    Dim frmChart As Access.Form
  6.    Set frmChart = Me.Controls("subfrmPivot2").Form
  7.    Set objChartSpace = frmChart.ChartSpace
  8.    Set objPivotChart = objChartSpace.Charts.Item(0)
  11. End Sub
It took long enough to get to there as I was having issues with the OWC10 reference, however that is now fixed and I cant face struggling on anymore.

Thanks for any help,

Aug 24 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.