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]

You can avoid the problem of having Excel rename the CurrentPage with the name of a missing PivotItem by using the .ClearAllFilters method.

The code below include some optional steps of providing a message to the user that the item wasn't found, and clearing the filters.

Private Sub ComboBox1_Change()

 Dim pt As PivotTable
 Dim sNewValue As String

 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

Exit Sub

ErrHandler:

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

 

[jacobsky]

You can avoid the problem of having Excel rename the CurrentPage with the name of a missing PivotItem by using the .ClearAllFilters method.

The code below include some optional steps of providing a message to the user that the item wasn't found, and clearing the filters.

Private Sub ComboBox1_Change()

 Dim pt As PivotTable
 Dim sNewValue As String

 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

Exit Sub

ErrHandler:

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

Hi,

Thank you, but I am getting a error "1004: Unable to get the PivotFields Property of the PivotTable Class"; when debug it highlights this section, specifically the with Pt.pivotfields("Provider Name").

In addition, I believe by clearing all filters it puts "All" in the filter? Is there a way to force blank or 0 data in when a filter doesn't exist? I do have some lookup functions on the list to that feeds to the combobox that I manually intend on checking to ensure each item is in each separate data set but if this can by bypassed it would be nice.

   'Optional: clear filters
   With pt.PivotFields("Provider Name")
      .ClearAllFilters
   End With

 

[Jerry Sullivan]

Regarding the error message, do you have one or more PivotTables on that sheet that doesn't have the field "Provider Name"?

Yes, clearing the Report Filters will put "All" in the filter. I'm not aware of a way to show no items that doesn't involve some complexity. A method that I've used in the past is to use a criteria-based filter on the first Row Field that will result in no matches. For example, Employee Name starts with "ZZZZ".

The complexity of this workaround arises from the need to manage (validate, store/reset previous filters) that first Row Field. For my use, I prefer to just clear the filters and advise the user that the item didn't exist.

 

[jacobsky]

I believe so, I only have 3 pivot tables. And Ive check all 3, I went into 1 table and pasted the field into each field on the others just to be sure, Ive also pasted each separately from the pivot table into cells and did a quick =A1=B1, A1=C1, B1=C1 test and all came back as true.

The odd thing is; when I change the combobox I get the error and I hit end. I go to the pivot table and sometimes 1 field is "ALL" and sometimes all the fields have the value I chose from the combo box. And even more interesting, when I double click into a any cell in the workbook and get out of it. It calculates and my pivot tables refresh with correct values and with the chosen filter with no error message.

I should note this is happening even if the selection is a valid filter in all three tables.

Another thing I have tried is "Ctrl + Alt + Shift + F9" and when I do that; I get the message ".....has no data in FinancePivot" however when I do a "Shift + F9" it fills in the correct data. Not sure if it matters but its the first pivot table on the sheet. This appears to happen for each selection.

A final note; I had other code in the file and I deleted it all out just to make sure that it wasn't impacting it at all.

Unless any of these raise a red flag, I am tempted to start from scratch and see if that makes a difference.

 

[Jerry Sullivan]

Add the keyword Stop near the beginning of the procedure so you can break the execution of the code to do some debugging.

 sNewValue = Me.ComboBox1.Value 
 Stop

After the code execution stops, use the F8 key to step through each line of code. This will give you an understanding of when the code is going to the error handler. At any point you can get information on a specific variable by entering a question mark followed by an expression that will result in a string in the Immediate Window of the VB Editor.

For example type this followed by <Enter> to find out which PivotTable is being processed.

?pt.Name

Hopefully this will shed some light on the cause.

 

[jacobsky]

Thanks for the debugging advice; I will definitely use this in the future when I have issues. However, I still couldnt figure out why it was getting hung up on the operations that were generating the error. I did recreate the whole file from scratch and it seems to be working as intended now. I am not sure what was holding it up but thank you for all your help and patience.

 

[jacobsky]

So I had it all working and then I changed something and got the error so I think I found what is causing the issue but I don't know how its related.

The list I have populating the combobox was initially a absolute reference name range: "Name: Lookup Refers to: $B2:$B270" when I change this to a dynamic named range Offset($B$2,0,0,counta($B:$B)-1,1) it causes the error. Does this make sense? If I need to update the named range manually each month thats okay but it would be preferable to have it dynamic.

I have changed this back and forth about 5 times to confirm that that is the only thing that is changing.

 

[Jerry Sullivan]

What is the specific error that is occurring?

Have you tried stepping though the code as I described in post #6 so that you can inspect the values of the variables when the code errors?

I don't know why changing the source of the combobox to a dynamic named range would cause that problem, but even if that is the cause we should be able to dig deeper to find why that generates an error.

 

[jacobsky]

Sorry if I am not to helpful with my description of the problem. When I go through the code when it works as intended(without dynamic named range); it goes to the first error and tells me

McWaters, Jessie not has not data in TopwRVUCode so its seems to recognize that it is the first case error type which makes sense.


When I run it with the dynamic named range it goes to

Case "Application-defined or object-defined error"

it skips the message then moves to.

[B]   Case Else[/B]
[B]      MsgBox Err.Number & ": " & Err.Description
[/B]

after I move through this code I get error message 1004: Unable to get the PivotFields Property of the PivotTable Class

I hit ok hit F8 again and get the error message again

When I look at the values in pt.name, sNewValue all appear to be correct.