Finding Our Selected Slicer Value

[NeedForExcel]

Hi,

I have an Excel Table (ListObject) which has a Slicer connected to it. (PivotTable is not available)

All I want is to retrieve the selected Value/Values on the slicer?

Can anyone help?

[CK76]

Something like below.

Please Login or Register  to view this content.

[NeedForExcel]

Hi,

Thank you very much!

Rep added..

[CK76]

You are welcome and thanks for the rep

[NeedForExcel]

Actually I had another requirement which I forgot to put up in the main post.

I Could retrieve the Selected Values using a bit different method than yours, however I would like to run the code as soon as the Slicer Values are selected on the slicer.

I am unable to do it. Any suggestions?

I am sorry if my question sounds basic, but I am not very good with VBA..

[CK76]

It's not a basic question

There is no Event handler that captures Slicer item selection. If the slicer was for PivotTable, you'd be able to use PivotTable_Update event to trigger the code.
There is Worksheet_TableUpdate event as well. However, since slicer does not update tables, this can't be used.

Workaround is to set up some out of way cell with following formula (I used cell K1)
=SUBTOTAL(3,Table1[ColumnName])=COUNTA(Table1[ColumnName])

It will return True when all items are visible (i.e. selected) and returns false otherwise.

Then you can use Worksheet_Calculate event like below.

Please Login or Register  to view this content.