Error when setting VBA pivotfields.currentpage

Question

I am trying to make a macro that will automaticly adjust the filters for several pivot tables based on user input, however if the user puts in something that isn't avaialble the code produces an error when trying to apply the filter.

Is there anyway to check which filters are available to select?

Example: One pivot table has three filters available (Year, Month, Type [Complaint, Praise, Both]) but if during a month there weren't any complaints then there is an error.

Code:

With PTable
    .PivotFields("Year").CurrentPage = Y
    .PivotFields("Month").CurrentPage = M
    .PivotFields("Type").CurrentPage = T 'Error line if T isn't valid
End With

Short and Dirty way... Sandwich that line between On Error Resume Next and On Error GoTo 0 — Siddharth Rout
– Siddharth Rout, Commented Feb 5, 2014 at 20:26
If you're in Excel 2010 or later, check into 'Slicers' -they allow you to use one filter to modify numerous pivot tables. This doesn't solve the VBA issue, but might be a simpler way to get at what you need. — Tim Sands
– Tim Sands, Commented Feb 5, 2014 at 20:32
Thanks guys. I was thinking about your method Siddharth Rout, but do you know if there is a way to check if the filter got applied. I would need to notify the user if a filter didn't get applied. — Chandler R Reeves
– Chandler R Reeves, Commented Feb 5, 2014 at 20:36
use err.clear just before the line, and check err.Number after. 0 is no error, anything else is an error — SeanC
– SeanC, Commented Feb 5, 2014 at 20:40

Siddharth Rout · Accepted Answer · 2014-02-05 20:40:04Z

3

Further to my comments, try this

With PTable
    .PivotFields("Year").CurrentPage = Y
    .PivotFields("Month").CurrentPage = M
    On Error Resume Next
    .PivotFields("Type").CurrentPage = T 'Error line if T isn't valid
    If Err.Number <> 0  Then
        Msgbox "Filter Didn't get applied"
    End If
    On Error GoTo 0
End With

answered Feb 5, 2014 at 20:40

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaffi Over a year ago

@Sid: Is there a way to actually check the value? I can get by with error catching, but I'd prefer validation if possible.

Collectives™ on Stack Overflow

Error when setting VBA pivotfields.currentpage

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related