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.

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

"Rick" wrote in message
...
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

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").CalculatedF ields("Daily Avg").Formula = _
"='Count Task'/21"

Case Else
activesheet.PivotTables("PivotTable2").CalculatedF ields("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" wrote in message
...
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").CalculatedF ields("Daily
Avg").Formula = _
"='Count Task'/21"

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

End Select

End Sub

Dick ,

Thank you so much - I GREATLY appreciate your help !

That tip worked perfectly !

Rick