pivot table dropdown doesn't trigger worksheet_change

[pkhoo]

Hi
i am new to Excel VBA but have some experience in Access.

I have a macro that will update the dependent pivot tables base on the first one using the worksheet_change trigger. However, when the pivot page dropdown was change, it didn't work - only if other cells was changed. If I use the _calculate event, it spreadsheet spins into an infinite loop.?? i appended the code below - hope somebody could help me

Private Sub Worksheet_Change(ByVal Target As Range)
'synchronize L3 parent location between pivot2 and dependent pivot3 pivot4
'Find name of page field
'PivotTable2 is the lead pivot table
'not working because change in pull down does not trigger worksheet change
'using w~ calc goes into infinite loop
MsgBox "hello"

PageField2 = ActiveSheet.PivotTables("PivotTable2&quot.PageFields(1)
pvt2page = ActiveSheet.PivotTables("PivotTable2&quot.PivotFields(PageField2).CurrentPage

PageField3 = ActiveSheet.PivotTables("PivotTable3&quot.PageFields(1)
ActiveSheet.PivotTables("PivotTable3&quot.PivotFields(PageField3).CurrentPage = pvt2page

PageField4 = ActiveSheet.PivotTables("PivotTable4&quot.PageFields(1)
ActiveSheet.PivotTables("PivotTable4&quot.PivotFields(PageField4).CurrentPage = pvt2page


End Sub

 

[xlbo]

To stop the infinite loop use

application.enableevents = false
'code
'code
'code
application.enableevents = true
Rgds
~Geoff~

 

[pkhoo]

thank you!
i got it to work! amazing
peter