Work change event

P

Perksy_no1

Well-known Member
Joined
Oct 27, 2011
Messages
598
Office Version
  1. 365
Platform
  1. Windows
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
Code: 
    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
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Guessing that it's a page field, you could try:
Code: 
If Not Intersect(Target, Range("B2")) Is Nothing Then
instead of your .Address test.
 
Upvote 0
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
 
Upvote 0
What code are you using and where does the error occur? (I'm guessing the error message doesn't really mention Workshops either ;))
 
Upvote 0
Worksheet! :LOL:

Heres the my code so far

Code: 
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
 
Upvote 0
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!
 
Upvote 0
Which part of the pivot table is the field "Req Period Current Month" in?
 
Upvote 0
Its in the pg field. Theres a chart attached to this pivot table as well
 
Upvote 0
How about:
Code: 
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
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Agnarr
  • Question
VBA code mix
Replies
1
Views
347
Joe4
Joe4
M
  • Question
faster vba code for creating a pivot
Replies
5
Views
750
Monicasinha
M
M
  • Question
Formatting and Charts lost/scrambled when exporting to PDF via macro
Replies
0
Views
560
Moosles
M
P
  • Question
VBA to copy multiple worksheets and either rename sheet with file name or add column with filename
Replies
5
Views
1K
I_know_nuffin
I
U
  • Question
VBA to look for an open file
Replies
1
Views
507
NateSC
NateSC

Forum statistics

Threads
1,214,617
Messages
6,120,541
Members
448,970
Latest member
kennimack

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top