Triggering worksheets events with changes in pivots

[Woodlander]

A change in a pivot cell (here: B1) does not work with the code below. Any ideas?

Private Sub Worksheet_Change(ByVal Target as Range)

If Target.Address = "$B$1" Then

MsgBox "Re-populate tables and print"
​

End If
​

End Sub
​

 

[Andrew Poulsom]

Have you tried the PivotTableUpdate event (Excel 2003 and above I think)?

 

[Woodlander]

Thanks Andrew - Can you help me with the coding?

 

[Andrew Poulsom]

Example:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    If Target.Name = "PivotTable1" Then
        MsgBox "PivotTable1 changed"
    End If
End Sub

 

[Woodlander]

Andrew - Thx.

I tried your suggested code, but it did not work.

 

[Andrew Poulsom]

In what way didn't it work? Where did you put the code? What's the name of your pivot table? What's actually in B1?

 

[Woodlander]

It did not work in any possible way.

Code is in the Sheet1 workbook; B1 is a cell at the top of the pivot table, that is used as a filter.

The pivot table's name is PivotTable1.

Thanks.

 

[Andrew Poulsom]

Is B1 actually part of the pivot table? It worked for me when I changed the selection in a page field.

 

[Woodlander]

B1 is part of my pivot table, and as a matter of fact, it is in the report filter section. I am sorry, but I don't see in your code any reference to any cell at all. Or is it that I have to embed your code into mine? Thanks again.

 

[Andrew Poulsom]

The code should work with a report filter, but I can't test it at the moment because I only have Excel 2003. I mentioned B1 because that was the cell you were testing in your original code.

Does the Worksheet_Calculate event fire when you select from the report filter?