G
Guest
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.
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.