Home Article Index Links Newsletter

Deze pagina in het NederlandsHome > Article Index > Pivottable Slicers >

Showing selected Slicer items on a worksheet

Even though it is often sufficient to see the slicer on the workbook it can be useful to be able to get a list of the filtered sliceritems in a worksheet cell. If your pivottable is built using a powerpivot model, then you can access this information by using the CUBERANKEDMEMBER worksheet function. If however the slicer was built off of a "normal" pivottable, then you need a bit of VBA code in a User Defined Function (UDF).

The slicer name

In order to retrieve the selected slicer items we need slicer's internal name, both for the CUBERANKEDMEMBER function and for the UDF. This name can be shown in the userinterface by right-clicking the slicer in question and selecting "Slicer settings". The name you are looking for is marked in red in the screenshot below and indicated by "Name to use in formulas":

Slicer settings dialog, needed name highlighted in red

CUBERANKEDMEMBER (for powerpivot slicers)

The CUBERANKEDMEMBER function returns an array of selected items and hence needs to be into as many (vertical) cells as you expect will be selected in the slicer. See the screenshot below (note the formula has ; as the delimiter, many users need to use the comma instead).

Using the CUBERANKEDMEMBER function

Note, that when less items are selected than the number of cells the function was entered into, the excess cells will show #N/A.

These #N/A's can be hidden with:

=IFERROR(CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$5)),"")

or counted with

=IF(ROW()-ROW($G$5)<=CUBESETCOUNT(CUBESET("PowerPivot Data",Slicer_Name)),CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()-ROW($G$5)),"")


The code sample below must be placed in a normal module:

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Items"
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
            GetSelectedSlicerItems = "No items selected"
        End If
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

The function takes the slicername as its argument and returns a text string (comma delimited) containing the selected items. In a worksheet cell the formula would look like this:



Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. I find Slicers a real gem in Excel 2010 and 2013. A great addition to the product!




Showing last 8 comments of 180 in total (Show All Comments):


Comment by: Lois (6-3-2020 11:20:00) deeplink to this comment

Hi Jan,

I used the VBA code and it seems to answer "No items selected" even though some items are selected. The excell worksheet does contain a Power Pivot link, so could this be the reason it doesn't work?

Comment by: Jan Karel Pieterse (9-3-2020 10:17:00) deeplink to this comment

Hi Lois,

Have you tried the CUBERANKEDMEMBER function I mentioned above?

Comment by: Kim (29-4-2020 23:25:00) deeplink to this comment

Is there a way to disable right click on slicers? I don’t want end user to manipulate my data, specially the “report connection” . Please help me solve this..thank you!!

Comment by: Jan Karel Pieterse (30-4-2020 11:37:00) deeplink to this comment

Hi Kim,

I tried to do that, but I failed. If you protect the sheet and set the slicer to locked, the slicer no longer works. The only way to achieve this is by adding ribbon customisation to the workbook and disabling the commands you don't want to allow access to. Even then, I bet there is still a way to get at what the user wants to do.
These things are hard to protect a user from doing. What you might do is add some VBA that checks the slicer settings and restores them to what you want them to be.

Comment by: Kim (30-4-2020 13:53:00) deeplink to this comment

How about, disabling report connections... on context menu? I am a newbie and I dont know how to do it..please help me, I appreciate your reply, thank you so much!! You helped me a lot too with the project I’m currently working on

Comment by: Jan Karel Pieterse (30-4-2020 14:15:00) deeplink to this comment

Hi Kim,

Sure. All you need is to add some RibbonX XML to your file:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <command idMso="SlicerConnectionsMenu" enabled = "false" />

Ron de Bruin describes how you add this to your file here:

Comment by: Michel Tremblay (5-1-2021 17:59:00) deeplink to this comment

A huge thank you for the udf function. She solved all my problems

Comment by: Jan Karel Pieterse (5-1-2021 18:01:00) deeplink to this comment

Hi Michel,

You're welcome!

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.