Thread: Filter Pivot Table with Hover

Hello!

I am trying to filter a pivot table using hover via a hyperlink. I did post about this yesterday but have revised my method.

I am using a hyperlink technique for changing a cell value as found on Chandoo.org. I have combined this with a Microsoft Pivot Table example that filters a Pivot Table based upon the values in cell D2 (attached example).

The code that powers the hyperlink is:

Public Function highlightSeries(seriesName As Range)
    Range("RegionFilterRange") = seriesName.Value
End Function

As you hover over C18, D18, E18, it will update the text in D2. This does not trigger a change in the Pivot Table. If you type text in D2 then the Pivot Table updates.

Private Sub Workbook_SheetChange does not seem triggered by the update by highlighSeries.

I've been going round in circles with different methods and can't get it work. Any ideas?

Thanks

I've updated the code highlightSeries:

Public Function highlightSeries(seriesName As Range)
    Range("RegionFilterRange") = seriesName.Value
    Call ThisWorkbook.UpdatePivotFieldFromRange("RegionFilterRange", "PivotTable1", "Region")

End Function

This calls the Public Sub but as with other approaches I have tried it seems that a Function called from a hyperlink cannot update the visibility of PIvotItems. It seems to be able to complete all other steps.

Is there any way to set the visibility of the items?

I've tried adding :

Field.AutoSort xlManual, Field

But this makes no difference.