Work change event

[Perksy_no1]

Hi There,

I was wondering if it possible to have a work sheet change event on a pivot table but only relelvent when changing a certain field?

I've tried the following piece of code but it doesnt seem to work

    If Target.Address(False, False) = "B2" Then
        
        Application.ScreenUpdating = False
        Application.StatusBar = "Getting latest order data"
        Application.Calculation = xlCalculationManual
        ActiveSheet.PivotTables("PivotTable20").PivotFields("Req Period Current Month").ClearAllFilters
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.StatusBar = False
        
    End If

Thanks in advance for any help you can offer

Mark

 

[RoryA]

Guessing that it's a page field, you could try:

If Not Intersect(Target, Range("B2")) Is Nothing Then

instead of your .Address test.

 

[Perksy_no1]

Hi Rory,

Thanks for having a look at this for me but Im getting the error code "Run time error '1004' Method 'Range' of object '_Workshop' failed

Just bit of back ground is what Im trying to do is if someone changes a setting in 1 of the pg field it sets a different filter to (all)

thanks

 

[RoryA]

What code are you using and where does the error occur? (I'm guessing the error message doesn't really mention Workshops either )

 

[Perksy_no1]

Worksheet!

Heres the my code so far

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        
        ActiveSheet.PivotTables("PivotTable20").PivotFields("Req Period Current Month").ClearAllFilters
        
    End If
End Sub

it crashes on the if not line

thanks

 

[Perksy_no1]

Hi Rory

Ignore my last email your part of the code is working fine. It crashes when trying to clear the filters so I end the macro then after that it crashes on the first line. just need to get it to clear the filters now

thanks!

 

[RoryA]

Which part of the pivot table is the field "Req Period Current Month" in?

 

[Perksy_no1]

Its in the pg field. Theres a chart attached to this pivot table as well

 

[RoryA]

How about:

Private Sub Worksheet_Change(ByVal Target As Range)
    On error goto err_handle
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        application.enableevents = false
        Me.PivotTables("PivotTable20").PivotFields("Req Period Current Month").ClearAllFilters

    End If
clean_up:
     application.enableevents = True
     Exit sub

err_handle:
   msgbox err.description
   resume clean_up
End Sub

 

[Perksy_no1]

Thanks for this Rory it looks like its clearing the filters on ("Req Period Current Month") problem is though it wont let select that field at all now!