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

Perhaps you'll find something helpful here...

http://www.contextures.com/pivottableindex.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

OR you can try...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
'Cancel EditMode and get Catalog Number
sCatNum = Target.Value: Cancel = True
With Sheets("Slicers (all)").PivotTables("PivotSlicer").PivotFields("Ca talog
#")
.ClearAllFilters
Application.EnableEvents = False
For Each vItem In .PivotItems
vItem.Visible = (vItem = sCatNum)
Next 'vItem
Application.EnableEvents = True
End With '...PivotFields("Catalog #")
End If '(Target.Column = 4)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops.., forgot to refresh...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
'Cancel EditMode and get Catalog Number
sCatNum = Target.Value: Cancel = True
With Sheets("Slicers (all)").PivotTables("PivotSlicer")
With .PivotFields("Catalog #")
.ClearAllFilters
Application.EnableEvents = False
For Each vItem In .PivotItems
vItem.Visible = (vItem = sCatNum)
Next 'vItem
Application.EnableEvents = True
End With '.PivotFields("Catalog #")
.RefreshTable
End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
End If '(Target.Column = 4)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

It's been a while since I've posted, and it's always a pleasure to
hear from you. I plugged this in, and it does indeed run; however,
unfortunately, it's still about as slow as the other other code. I
think it's because it's actually loading the pivot table with every
iteration of the loop. And there are over 300 pivot items to load.

When I actually go to the pivot table and select a catalog number from
the slicer, it loads in just a couple seconds. Is there a reason I'm
not able to simply clear the filters and load the catalog number that
gets put into the variable from the double-click event?

Thanks Garry!

Frank

On Fri, 06 Apr 2018 07:35:45 -0400, GS wrote:

Oops.., forgot to refresh...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
'Cancel EditMode and get Catalog Number
sCatNum = Target.Value: Cancel = True
With Sheets("Slicers (all)").PivotTables("PivotSlicer")
With .PivotFields("Catalog #")
.ClearAllFilters
Application.EnableEvents = False
For Each vItem In .PivotItems
vItem.Visible = (vItem = sCatNum)
Next 'vItem
Application.EnableEvents = True
End With '.PivotFields("Catalog #")
.RefreshTable
End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
End If '(Target.Column = 4)

Hi Garry,

It's been a while since I've posted, and it's always a pleasure to
hear from you. I plugged this in, and it does indeed run; however,
unfortunately, it's still about as slow as the other other code. I
think it's because it's actually loading the pivot table with every
iteration of the loop. And there are over 300 pivot items to load.

When I actually go to the pivot table and select a catalog number from
the slicer, it loads in just a couple seconds. Is there a reason I'm
not able to simply clear the filters and load the catalog number that
gets put into the variable from the double-click event?

Thanks Garry!

Frank

On Fri, 06 Apr 2018 07:35:45 -0400, GS wrote:

Oops.., forgot to refresh...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
'Cancel EditMode and get Catalog Number
sCatNum = Target.Value: Cancel = True
With Sheets("Slicers (all)").PivotTables("PivotSlicer")
With .PivotFields("Catalog #")
.ClearAllFilters
Application.EnableEvents = False
For Each vItem In .PivotItems
vItem.Visible = (vItem = sCatNum)
Next 'vItem
Application.EnableEvents = True
End With '.PivotFields("Catalog #")
.RefreshTable
End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
End If '(Target.Column = 4)

The pivot table functions are written in C++ which is *orders of magnitude*
faster than VB. I was hoping the link I posted would contain some way to
automate the slicer. Optionally, turn on the macro recorder and see what code
(if any) it generates when you do it manually.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Fri, 06 Apr 2018 20:42:10 -0400, GS wrote:

Hi Garry,

It's been a while since I've posted, and it's always a pleasure to
hear from you. I plugged this in, and it does indeed run; however,
unfortunately, it's still about as slow as the other other code. I
think it's because it's actually loading the pivot table with every
iteration of the loop. And there are over 300 pivot items to load.

When I actually go to the pivot table and select a catalog number from
the slicer, it loads in just a couple seconds. Is there a reason I'm
not able to simply clear the filters and load the catalog number that
gets put into the variable from the double-click event?

Thanks Garry!

Frank

On Fri, 06 Apr 2018 07:35:45 -0400, GS wrote:

Oops.., forgot to refresh...

Dim sCatNum$, vItem
If (Target.Column = 4) Then
'Cancel EditMode and get Catalog Number
sCatNum = Target.Value: Cancel = True
With Sheets("Slicers (all)").PivotTables("PivotSlicer")
With .PivotFields("Catalog #")
.ClearAllFilters
Application.EnableEvents = False
For Each vItem In .PivotItems
vItem.Visible = (vItem = sCatNum)
Next 'vItem
Application.EnableEvents = True
End With '.PivotFields("Catalog #")
.RefreshTable
End With 'Sheets("Slicers (all)").PivotTables("PivotSlicer")
End If '(Target.Column = 4)

The pivot table functions are written in C++ which is *orders of magnitude*
faster than VB. I was hoping the link I posted would contain some way to
automate the slicer. Optionally, turn on the macro recorder and see what code
(if any) it generates when you do it manually.

Thanks Garry. I haven't had a chance to look at the C++ in the link
yet. Will do that tonight. In the meantime, I recorded the macro,
and below is an excerpt of what it returns. There are actually over
300 items in the list. So, the macro above is looping through each of
those items, as the code below more or less is doing, but the code
above takes about 45 seconds to load, whereas the recorded code below
is almost instantaneous. Is there not a way to turn off all the
slicer items at once, and then turn on just the one I need? That's
what I've been looking for but have not been able to find. Thanks
Garry.


Sub Macro1()
With ActiveWorkbook.SlicerCaches("Slicer_Catalog")
If mySlicerItem = .SlicerItems("277096275") Then .Selected =
True
.SlicerItems("277096276").Selected = False
.SlicerItems("277096325").Selected = False
.SlicerItems("1100000000").Selected = False
.SlicerItems("1900013000").Selected = False
.SlicerItems("1900031000").Selected = False
.SlicerItems("2108100000").Selected = False
.SlicerItems("2108105000").Selected = False
.SlicerItems("2108151000").Selected = False
.SlicerItems("2108352000").Selected = False
.SlicerItems("2296003108").Selected = False
.SlicerItems("2296003111").Selected = False
.SlicerItems("2296003125").Selected = False
.SlicerItems("2296003511").Selected = False
.SlicerItems("2296301000").Selected = False
End With
End Sub