Toggle datafield in Pivottable
 

Hi all,

I don't know exactly what I have done, but the following code stop working.

All I want to do is simple; I have a Data validation cell, named
"data_field", that lets users choose one item from "Bid", "Offer" or "Mid".
Then, the choice is reflected to Pivottable data fields. Of course my data
source have three fields; Bid, Offer and Mid.

Here is the code:
---
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("data_field").Address Then
Application.EnableEvents = False

With Me.PivotTables("PivotTable2")
.PivotFields(Target.Value).Orientation = xlDataField
.PivotFields(.DataFields(1).Name).Orientation = xlHidden

.RefreshTable
End With
Application.EnableEvents = True
End If
End Sub
---

Initially I have "Sum of Bid", "Sum of Offer" and "Sum of Bid" in Data
fields. Application.EnabledEvents = False/True prevents recursive calls to
Worksheet_Change itself.

What is a better way of achieving this? Sorry, I am VERY new to this
Pivottable programming...

I am using Excel 2002 SP-2 on Win2K Pro.

Thanks for your time.
---
Tetsuya Oguma, Singapore

Toggle datafield in Pivottable
 

I am at this stage almost certain that the code stopped working after the
file was saved.

I am now just thinking of how to use PivotCache to prevent this from
happening.

Thanks,