excel - How to trigger an update after a (non-pivottable) Table linked Slicer Update occurs

Question

I need to, via VBA, make a note of the values (in a cell) selected on a slicer. So in a slicer with options Option A, Option B, Option C I want some code to trigger that puts Option A|Option C into a cell, for example. How do I trigger that code to start with? I can find no way to begin that event, I've tried right-click, assign-macro / NEW, but that just triggers an event when you CLICK and doesn't let you update the slicer.

Answer 1

While there are no ways I can think of to directly call an event tied to a slicer; I have thought about it some and realized that you can handle your event in the Worksheet_Calculate event if you include a volatile function (function that gets recalculated after each action) such as =NOW() or =RAND() hidden somewhere on the worksheet (either in a hidden column, or change the font color to blend in so as to be invisible to the user).

Every time you change the slicer parameters, that function will be recalculated which will call the Worksheet_Calculate event. In that event you can retrieve selected items and display them in a cell.

Open the VB Editor and paste the following code into the worksheet code of whichever sheet you want this functionality on:

Private Sub Worksheet_Calculate()
        Dim item As SlicerItem
        Dim strOutput As String

        'Get all selected items from the first slicer
        For Each item In ActiveWorkbook.SlicerCaches(1).SlicerItems
                If item.Selected = True Then
                        strOutput = item.Caption & "|" & strOutput
                End If
        Next item

        'If we don't pause macros when we modify the
        'cell value, this function will get called again and
        'start an infinite loop, so we disable macros for
        'the update of the cell value

        Application.EnableEvents = False

        'Update a cell value with the slicer output
        Me.Range("G1").Value = "|" & strOutput

        Application.EnableEvents = True

End Sub

In my test workbook I added a table containing information about authors and books (author name, book title, price, rating, etc.) and I added a slicer for Author (this is the slicer that ActiveWorkbook.SlicerCaches(1).SlicerItems refers to).

The above code will output the selected options in the Author slicer to cell G1 (which you may change to suit your needs). I placed the =NOW() function in cell L2 and made the text white so as to appear invisible to the user.

The result of selecting items within the slicer is cell G1 looking like:

|Clive Barker|Stephen King|

Assuming those two authors were selected.

A snap of output after the code executes when I selected H.P. Lovecraft and Clive Barker.

This will work for whatever is selected, and of course you can change the referenced slicer as needed. Be sure to keep the Application.EnableEvents = False and Application.EnableEvents = True trigger around the part of the code that modifies the cell value within the Worksheet_Calculate event, otherwise the code will enter an infinite loop.

I added the "|" to the beginning of the string to add to cell G1 to make it look better (looked awkward with a trailing |) but you could just as well trim the trailing | off by using Me.Range("G1").Value = Left(strOutput, Len(strOutput) - 1) instead of Me.Range("G1").Value = "|" & strOutput.

This would make the output look like this:

H.P. Lovecraft|Clive Barker

Again assuming those two authors were selected in the slicer. If you selected three, you would get:

H.P. Lovecraft|Stephen King|Clive Barker

Etc.