Hi. I've got Windows 7 and Windows 10, Excel 2016 (version 1708 build
8431.2236). I've got a pivot table on one sheet that is connected to a
data Table on another, and the pivot table has slicers connected to
it. I'm trying to take a cell value that is on yet another sheet and
filter that pivot table with that value. I want to use the
DoubleClick event, where the user double clicks the value, which loads
into the pivot table filter on the other sheet. Seems simple enough,
and I've used the double click event before with success, but just not
with a pivot table. There is a plethora of simple code out that
showing how to do that, but with all iterations of code, I keep
getting an error message that says, "Unable to set the CurrentPage
property of the PivotField class".

I've checked all of my options in Excel, and I've tried this on other
computers with the same result, and I've looked at must be 100 other
forum and Google sites with no luck, so I must be doing something
wrong. The first module shown below is that code. Below that is
another module that a coworker made that does work, but it is SLOW!!

I'd appreciate any help and advice. Thank you!

===============

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim iCol As Long
Dim myCatNum As String

iCol = ActiveCell.Column

'case structure not needed, but borrowed from another procedure.
Select Case iCol

Case 4
myCatNum = ActiveCell.Value
Sheets("Slicers (all)").Select
ActiveSheet.PivotTables("PivotSlicer").PivotFields ("Catalog
#").ClearAllFilters
ActiveSheet.PivotTables("PivotSlicer").PivotFields ("Catalog
#").CurrentPage = myCatNum
Case Else
Exit Sub
End Select

End Sub

=======================
'this one works...but it is SLOW at about 45 seconds to load the table
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim iCol As Long
Dim myCatNum As String
Dim pt As PivotTable
Dim ptItem As PivotItem
Dim Field As PivotField

iCol = ActiveCell.Column

Select Case iCol

Case 4
myCatNum = ActiveCell.Value
Sheets("Slicers (all)").Select
Set pt = Worksheets("Slicers
(all)").PivotTables("PivotSlicer")
Set Field = pt.PivotFields("Catalog #")

Field.ClearAllFilters
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.RefreshTable

Application.EnableEvents = False
With Field
For Each ptItem In .PivotItems
If ptItem = myCatNum Then
ptItem.Visible = True
Else: ptItem.Visible = False
End If
Next
Application.EnableEvents = True
End With
End Select

pt.RefreshTable

End Sub