pivottable page fields

[Guest]

Hi there,

Is there any way to return the actual page field that was
changed from the Private Sub Worksheet_Change(ByVal
Target As Range) procedure?

The value Target returns is the entire range of the
pivottable, not just the page field range cell that was
changed.

Any ideas?

Grant.

 

[Debra Dalgleish]

You could use a module level variable to track the page field values.
For example:

'===========================
Option Explicit
Dim mvPivotPage1 As Variant
Dim mvPivotPage2 As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField

Set pt = ActiveSheet.PivotTables(1)
Set pf1 = pt.PageFields(1)
Set pf2 = pt.PageFields(2)

If LCase(pf1.CurrentPage) _
<> LCase(mvPivotPage1) Then
MsgBox "Page field " & pf1.Name & " was changed"
Else
If LCase(pf2.CurrentPage) _
<> LCase(mvPivotPage2) Then
MsgBox "Page field " & pf2.Name & " was changed"
End If
End If
mvPivotPage1 = pf1.CurrentPage
mvPivotPage2 = pf2.CurrentPage

End Sub
'=============================