OccasionalGeek
New Member
- Joined
- Mar 1, 2012
- Messages
- 12
Win 7, Excel 2010, VBA.
Using a "Sub Worksheet_PivotTableUpdate" 'trigger', I want to determine WHICH filter changed if a user made a new selection from a drop down list on the filter pivot field. (not the value that was chosen, but the field that was changed.)
The macro initiates whenever the user makes a new selection, no problem. But how do I identify the name (or index number) of the pivot field that changed? (there are 13 members of the PivotFields object). I can identify the pivot table (.name), and could get value it was changed to easily if I knew which pivot field changed. There are several pivot tables on the sheet.
All of the following code works fine - but I don't know how to tell which pivot field changed? (btw: The vctr.name and vctr.value both return identical data every time.) The code is on the sheet containing the pivot tables, of course.
Using a "Sub Worksheet_PivotTableUpdate" 'trigger', I want to determine WHICH filter changed if a user made a new selection from a drop down list on the filter pivot field. (not the value that was chosen, but the field that was changed.)
The macro initiates whenever the user makes a new selection, no problem. But how do I identify the name (or index number) of the pivot field that changed? (there are 13 members of the PivotFields object). I can identify the pivot table (.name), and could get value it was changed to easily if I knew which pivot field changed. There are several pivot tables on the sheet.
All of the following code works fine - but I don't know how to tell which pivot field changed? (btw: The vctr.name and vctr.value both return identical data every time.) The code is on the sheet containing the pivot tables, of course.
Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim vCtr As Variant
' MsgBox "Update"
With Target
MsgBox "Target Name ' " & .Name
MsgBox "Pivotfields count = " & .PivotFields.Count
For Each vCtr In .PivotFields
MsgBox "Pivotfields name = " & vCtr.Name
MsgBox "Pivotfields value = " & vCtr.Value
Next vCtr
End With
End Sub
