Error handling pivot field VBA

[muhleebbin]

Hi!

I have the following code however on occasion I do run into an error when the pivot field (cat) that the code is looking for does not exist. Can someone help with the error handling code that would eliminate this?

Thank you in advance!

    Dim pt As PivotTable
    Dim field As PivotField
    Dim res As PivotField
    Dim cat As String

    Application.ScreenUpdating = False
    
    Set pt = Worksheets("MIR Pivot").PivotTables("PivotTable1")
    Set field = pt.PivotFields("PropertyName")
    Set res = pt.PivotFields("Resident")
    cat = Worksheets("MIR Pivot").Range("G2").Value

    With pt
        field.ClearAllFilters
        field.CurrentPage = cat
        pt.RefreshTable
    End With

 

[Domenic]

Try...

    With field
   
        .ClearAllFilters
   
        On Error Resume Next
        .CurrentPage = cat
        On Error GoTo 0
   
    End With

Or, to display a message when the pivot item doesn't exist, try...

    With field
   
        .ClearAllFilters
   
        On Error Resume Next
        .CurrentPage = cat
        If Err.Number <> 0 Then
            MsgBox "'" & cat & "' not found.", vbInformation
        End If
        On Error GoTo 0
   
    End With

Note that you shouldn't need to refresh the pivot table after assigning a value to CurrentPage. However, when you do want to refresh your pivot table, you don't need the With/End With statement. You can simply use the following line of code..

pt.RefreshTable

Hope this helps!

 

[muhleebbin]

Domenic,

Thank you very much for this fix and it totally works! Would you mind assisting with a minor tweak to it? I just realized that when a location is selected but there is no data for it, the pivot table will then select all with this version of the error handling. Can it be tweaked where it would select either the "" or "(blank)"?

Basically a user selects a location via a drop down list which then triggers this filter. The pivot will then populate data but if there shouldn't be any for a location, I don't want it to show all.

Also thank you for the tip on the pt.RefreshTable!

Hope this makes sense and thanks again for your help!

 

[Domenic]

In that case, try the following instead...

    Dim PvtItm As PivotItem
    
    With field
    
        .ClearAllFilters
    
        On Error Resume Next
        Set PvtItm = .PivotItems(cat)
        If Err.Number = 0 Then
            .CurrentPage = cat
        Else
            .CurrentPage = "(blank)"
        End If
        On Error GoTo 0
  
    End With

Hope this helps!

 

[muhleebbin]

It doesn't seem to want to do the else part

When I select a location that is valid it will select it correctly but when a location that is invalid is selected it still shows all. I tried flipping the cat and "(blank)" to test and when an invalid selection is selected it does change to "(blank)" but when a valid selection is selected it does not match the pivotitem but instead still selects "(blank)"

Sorry for the troubles but again thank you for your assistance!

 

[Domenic]

Does (blank) appear in the dropdown for your page field?

If you enter the following line of code in the Immediate Window (Visual Basic Editor >> View >> Immediate Window or Ctrl+G), does it update the pivot table accordingly? Or do you get an error?

? Worksheets("MIR Pivot").PivotTables("PivotTable1").PivotFields("PropertyName").CurrentPage = "(blank)"

 

[muhleebbin]

No idea why but it does not update the pivot accordingly.....everything looks correct and "(blank)" is an option

 

[Domenic]

If you enter the following line of code in the Immediate Window (Visual Basic Editor >> View >> Immediate Window or Ctrl+G), does it update the pivot table accordingly? Or do you get an error?

? Worksheets("MIR Pivot").PivotTables("PivotTable1").PivotFields("PropertyName").CurrentPage = "(blank)"

When you enter the above line in the Immediate Window, do you get an error? If so, which error do you get?

 

[muhleebbin]

When you enter the above line in the Immediate Window, do you get an error? If so, which error do you get?

Sorry there are moments where i'm a complete moron.....upon hitting enter it shows false

 

[Domenic]

Sorry, I mistakenly added a question mark (?) at the beginning of the code. It should be...

Worksheets("MIR Pivot").PivotTables("PivotTable1").PivotFields("PropertyName").CurrentPage = "(blank)"