Dynamically changing a calculated field in Pivot Table

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Hi - I want to be able to change the formula in a
calculated field of a pivot table when the user updates
the table by choosing an item from a page field.

I know very, very little VBA, but can see by recording a
macro how the formula is changed. What I need help with
is how to do this dynamically when the user changes the
pivot table.

Any help would be appreciated !

thx,

Rick
 
Rick

You can use the Worksheet_Calculate event. This will fire when the pivot
table page is changed (in fact, any time the worksheet is calculated). In
it, you can check the value of the page field and change the formula if
needed.

To code in that event, right click on the sheet tab and choose View Code.
Select Worksheet and Calculate from the drop down boxes at the top of the
pane to insert the Sub and End Sub statements, then code away.

I don't work with pivot tables very much, but if you have some questions
about how to get the page value or change the formula, post back with some
details.
 
Thamks for the info !

It works, but creates a new issue for me. It seems that when I change the calculated field, it fires the worksheet_calculate event and it kind of goes through an endless loop. Worksheet calculate event fires - my formula changes which again triggers the calculate event . My code is below, basically the field I am trying to "capture" is when the month is chosen in the page field. I will be expanding that select/case statement for other months once I get past this issue. Thanks for your help !

Rick

Private Sub Worksheet_Calculate()

Select Case activesheet.PivotTables("PivotTable2").PivotFields("Month").CurrentPage

Case "Sep-03"
activesheet.PivotTables("PivotTable2").CalculatedFields("Daily Avg").Formula = _
"='Count Task'/21"

Case Else
activesheet.PivotTables("PivotTable2").CalculatedFields("Daily Avg").Formula = _
"='Count Task'/1"

End Select

End Sub
 
Rick

Put this as the first line of your sub

Application.EnableEvents = False

and this at the end of your sub

Me.Calculate
Application.EnableEvents = True

That will prevent the event from being called over and over.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Rick said:
Thamks for the info !

It works, but creates a new issue for me. It seems that when I change the
Click to expand...
calculated field, it fires the worksheet_calculate event and it kind of goes
through an endless loop. Worksheet calculate event fires - my formula
changes which again triggers the calculate event . My code is below,
basically the field I am trying to "capture" is when the month is chosen in
the page field. I will be expanding that select/case statement for other
months once I get past this issue. Thanks for your help !
 
Dick ,

Thank you so much - I GREATLY appreciate your help !

That tip worked perfectly !

Rick
 
Back
Top