Function using Data Dictionary to read value from slicer

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
215
Office Version
  1. 2016
Platform
  1. Windows
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.


VBA Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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 . . .

VBA Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    'call your macro or function
    
End Sub

Hope this helps!
 
Upvote 0
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 . . .

VBA 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?

1639620946062.png
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top