VBA Pivot Table Filter

[jacobsky]

[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]I am trying to create a dashboard where the user picks an item in a combo box and that is filtered to all the pivot tables on another sheet. The code seems to be working fine except when the filtered item doesn't exist in a specific pivot table. [/FONT]

[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]What is happening is, it is forcing that filtered item into the pivot table and is using the previously filtered data as its data. When I try to manually recalculate it nothing happens. I then went to the source table and added the filtered item and filled it with 0's for the data fields in hopes that it would just pull the 0s; however, it seem to be stuck on the previously filtered forced data set. When I try to dig into the data (right click show details) it brings up the data from the forced data set. It almost like its forced saved in the background and I cant seem to overwrite it. In the pivot table options, under the data tab I have "Retain Items Deleted from Data Source" selected to None but I have tried all 3 options and none work.[/FONT]

[FONT=Open Sans, Helvetica Neue, Helvetica, Arial, sans-serif]Below is the code I used. I should warn, I am not a coder by stretch of the imagination. I am just relatively savy in excel and get chosen for projects that I sometimes need to use code for. Typically I search the internet and find code from forums and copy paste. And then I modify through trial and error until I get it to work. [/FONT]

[FONT=&quot]Private Sub ComboBox1_Change()[/FONT]
[FONT=&quot]Dim sheet As Worksheet
Dim pt As PivotTable
Dim ptField As PivotField[/FONT]
[FONT=&quot]Set sheet = ThisWorkbook.Worksheets("PivotTables")[/FONT]
[FONT=&quot]For Each pt In sheet.PivotTables[/FONT]
[FONT=&quot]Set ptField = Nothing[/FONT]
[FONT=&quot]On Error Resume Next[/FONT]
[FONT=&quot]Set ptField = pt.PivotFields("Provider Name")[/FONT]
[FONT=&quot]ptField.CurrentPage = Me.ComboBox1.Value[/FONT]
[FONT=&quot]Next pt[/FONT]
[FONT=&quot]End Sub[/FONT]

 

[Jerry Sullivan]

I was able to replicate that behavior in a mockup of your workbook.

The problem is that each time a PivotTable's filters are changed, Excel does a recalculation of the dynamic named ranges. Because the ListRows property of the combobox is bound to the DNR range, it triggers another call to the ComboBox1_Change event. When the ComboBox1_Change event triggers itself, the error arises.

Temporarily setting the Application.EnableEvents property to False, is often used to keep Worksheet Events from triggering themselves. That won't help in this case since .EnableEvents does not affect Controls' events.

I'd suggest that you use this workaround that is used to handle the same problem with Userform controls. It doesn't prevent the code from calling itself, but it uses a variable flag "mbChangeEventRunning" to immediately exit the ComboBox1_Change procedure if the flag has the value True.

[COLOR="#0000CD"]Dim mbChangeEventRunning As Boolean[/COLOR]

Private Sub ComboBox1_Change()
 Dim pt As PivotTable
 Dim sNewValue As String

[COLOR="#0000CD"] If mbChangeEventRunning Then
   Exit Sub
 Else
   mbChangeEventRunning = True
 End If[/COLOR]
 
 sNewValue = Me.ComboBox1.Value
 
 For Each pt In ThisWorkbook.Worksheets("PivotTables").PivotTables
  
   On Error GoTo ErrHandler
   With pt.PivotFields("Provider Name")
      .ClearAllFilters
      .CurrentPage = sNewValue
   End With
 Next pt

[COLOR="#0000CD"] '--reset flag
 mbChangeEventRunning = False[/COLOR]

Exit Sub

ErrHandler:
 Select Case Err.Description
   Case "Application-defined or object-defined error"
      MsgBox sNewValue & " has no data in " _
         & pt.Name
   Case Else
      'Optional: message to user
      MsgBox Err.Number & ": " & Err.Description
   End Select
 
   'Optional: clear filters
   With pt.PivotFields("Provider Name")
      .ClearAllFilters
   End With
   Resume Next
End Sub