+ Reply to Thread
Results 1 to 8 of 8

Limiting Slicer Selection based on user access

  1. #1
    Registered User
    Join Date
    08-22-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    5

    Limiting Slicer Selection based on user access

    Hi All,

    Things that I was trying to achieve
    1. Enable full access to certain users only based on user login ID. Limited access for the rest of users.
    2. Users with limited access can only select maximum 5 items in slicer. When it hits more than 5, an error message should show up and (ideally) the last selected slicer item should be deselect. It can also be - clear all selected items and select only one item (any item) in the slicer. My ultimate goal is to stop limited access user to select more than 5 items at once.

    I managed to do the first one but stuck at the second one. I couldn't use "undo" because apparently selecting an item in slicer does not consider as an action to Excel. Hence if user don't have any last action in excel, he/she will be prompted error and it does not deselect the last selected slicer item.

    Can anyone help me with this?

  2. #2
    Forum Contributor
    Join Date
    12-02-2010
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2013, 2016
    Posts
    100

    Re: Limiting Slicer Selection based on user access

    Hello lolaryong,

    You could potentially set up a list of allowed users on a xlVeryHidden and/or password protected sheet and then compare that list using:
    Please Login or Register  to view this content.
    That would allow you to see the username of the person utilizing the sheet, and then if it isn't on the approved list limit some functionality.

    Hope that helps,

    DarkF1ame
    Mark as "Solved" and add Reputation if applicable.

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Limiting Slicer Selection based on user access

    I will post some VBA for you when I get a chance tomorrow (UK)

    I understand that limited access user should only be able to select a maximum of 5 slicer items.
    Is the limited access user permitted to select any 5 items?
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  4. #4
    Registered User
    Join Date
    08-22-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    5

    Re: Limiting Slicer Selection based on user access

    Quote Originally Posted by kev_ View Post
    I will post some VBA for you when I get a chance tomorrow (UK)

    I understand that limited access user should only be able to select a maximum of 5 slicer items.
    Is the limited access user permitted to select any 5 items?
    Yes @kev. Limited access user can select any 5 items at once.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Limiting Slicer Selection based on user access

    Test in attached workbook
    - user names without restriction are held in array variable "NoRestriction"
    - when workbook opens, UserName is checked against names in the array
    - workbook level variable "Restricted" is set to TRUE or FALSE
    - user informed via message box
    - pivot table is on sheet "PT"
    - slicer defaults to Area01
    - to select more areas toggle multi-select icon
    - if "Restricted" = TRUE then selection max =5 areas

    To add a user with no restriction, add a name to the array
    - names are case sensitive
    Please Login or Register  to view this content.

    In ThisWorkbook module:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    In Module1 (general module):
    Please Login or Register  to view this content.
    In Sheet module:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-22-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    5

    Re: Limiting Slicer Selection based on user access

    Thank you so much kev! I managed to use the code with a little tweak to cater more than 1 slicers.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Limiting Slicer Selection based on user access

    How about posting the modified code so that we all learn from your efforts?

  8. #8
    Registered User
    Join Date
    08-22-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    5

    Re: Limiting Slicer Selection based on user access

    sure kev, here you go.
    i am actually looking for a way to keep the array public/global to enable access from all worksheets but couldn't figure it out so i proceed with this way.
    Attached Files Attached Files

+ 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. Refresh Pivot Data based on slicer selection
    By skate1991 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2017, 11:59 AM
  2. Limiting a pivot table slicer to one selection
    By behrensf84 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-14-2014, 09:25 PM
  3. Limiting Slicer multiple selection
    By Paul-NYS in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-17-2012, 02:58 PM
  4. Slicer Macro - unfilter another slicer on selection
    By chadheins in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2012, 10:41 AM
  5. [SOLVED] Limiting a Slicer's data
    By JimDandy in forum Excel General
    Replies: 2
    Last Post: 04-27-2012, 04:59 PM
  6. Limiting custom menu selection based on sheet name
    By RobertY in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-13-2008, 09:07 AM
  7. [SOLVED] Pivot - limiting user access with VBA
    By Christian in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2005, 09:35 AM

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