Function using Data Dictionary to read value from slicer

[Guinaba]

Hi guys,

Trying to create a function that reads the value from the selected slicer's button in a cell. I am using data dictionary for that, but not sure where the error is, when I change the selection in the slicer the function doesn't get updated.

Function SlicerReader() As Dictionary
  Dim sc As SlicerCache
  Dim si As SlicerItem
  Dim dctItem As Object
  Set dctItem = CreateObject("Scripting.Dictionary")

  Set sc = ActiveWorkbook.SlicerCaches("Slicer_ItemID_Desc")

  For Each si In sc.SlicerItems
    If si.Selected Then
        dctItem.Add si.Value, si.Value
        ActiveWorkbook.Sheets("Total_TPRP").Range("L5").Value = dctItem.Items
    End If
  Next

  Set dctItem = Nothing
 
End Function

 

[Domenic]

You can use the PivotTableUpdate event handler, which will be executed each time your pivottable is updated, whether directly or by slicer. And when it gets executed, simply call your function from there. So, for example, right-click the sheet tab for the sheet containing your pivottable, select View Code, and copy/paste the following code . . .

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    'call your macro or function
    
End Sub

Hope this helps!

 

[Guinaba]

You can use the PivotTableUpdate event handler, which will be executed each time your pivottable is updated, whether directly or by slicer. And when it gets executed, simply call your function from there. So, for example, right-click the sheet tab for the sheet containing your pivottable, select View Code, and copy/paste the following code . . .

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    'call your macro or function
   
End Sub

Hope this helps!

Thanks @Domenic! That is a good idea!
I did that but not sure what I am missng here. The function still not working? Do I have to speficy the pivot table name?

 

[Domenic]

When you say that it's not working, can you be specific? Are you getting an error? If so, what type of error are you getting and on which line?

Did you make sure to add the event handler to the code module for the sheet containing the pivottable?

Did you make sure to enable macros?

Where did you place your function SlicerReader? In the same code module for the sheet containing your pivottable or in a regular module? Or somewhere else?