+ Reply to Thread
Results 1 to 6 of 6

My solution for only allowing one selected slicer item

  1. #1
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    My solution for only allowing one selected slicer item

    Hi all,

    I am working on a project that uses several pivot tables and has one slicer to rule them all, and in the datasource bind them.

    I needed this slicer to be restricted to select only one item at a time (in my case, Bill Month in "mmm-yy" format).

    I searched online and couldn't find any way to do this without VBA, which I beleive is correct. However the VBA solutions I found seemed over-complicated to achieve the solution. I thought I would post the code I wrote to solve this problem to add it to the collective.

    I have my slicer sorted by "Newest to Oldest" so that in the case of multi-selection, the latest date is selected instead because it is higher up on the list.

    See below:
    Please Login or Register  to view this content.
    Replace the slicer name with your slicer's formula name and give it a try.

    Hopefully there are enough keywords in this post so it gets stumbled upon by those who seek it.

    Working in Excel 2010.
    Last edited by Stormin'; 04-05-2017 at 11:31 AM.

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: My solution for only allowing one selected slicer item

    By default when you click an item in a slicer, all other items deselect. To select multiple items you do ctrl+click. The goal of your code is to make impossible to select multiples, correct?

  3. #3
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: My solution for only allowing one selected slicer item

    That is correct. When the user uses CTRL + CLICK or SHIFT + CLICK to select multiple slicers, the code will deselect all but the first selected item in the slicer.

  4. #4
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: My solution for only allowing one selected slicer item

    Works perfectly, thank you! I will add this to my database of cool and useful excel stuff!

  5. #5
    Registered User
    Join Date
    02-17-2011
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: My solution for only allowing one selected slicer item

    Thanks a lot for the post.
    Last edited by AliGW; 11-17-2020 at 04:21 AM. Reason: PLEASE don't quote unnecessarily!

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,744

    Re: My solution for only allowing one selected slicer item

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Run macro to change pivot table row field when user selected a slicer item
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2016, 09:44 AM
  2. Help with the code to assign a variable to Slicer Item
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2016, 02:00 PM
  3. [SOLVED] Create pdf for every slicer item...vba error
    By jeroenft in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2015, 04:56 PM
  4. [SOLVED] Create pdf print of every slicer item
    By jeroenft in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-25-2015, 08:13 AM
  5. Workbook_Open to select first item in slicer/pivot
    By kev_33 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2015, 01:08 AM
  6. Selecting a Slicer Item in VBA
    By ManUBlueJay in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2014, 08:21 AM
  7. How to auto select from a Slicer last or top item
    By xtrenge in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-07-2014, 06:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1