Consulting

Results 1 to 20 of 20

Thread: Can you lock one filter in a pivot table?

  1. #1
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location

    Can you lock/disable one filter in a pivot table?

    Hello,

    I am working on an excel file that will be sent out to multiple stakeholders. It contains a pivot table with sensitive data. I do not want department heads to be able to go into the pivot table and use the dropdown on one of the filters at the top of the pivot to look at other departments' data. In other words, I want to lock/disable one of the filters at the top of the pivot table so it is not accessible. Is it possible to lock/disable one filter at the top of the pivot table?

    Any help appreciated
    Last edited by Ray707; 04-06-2021 at 03:28 AM.

  2. #2
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    I found this macro (under 'disable selection') that disables all selection, I'm thinking whether this could be tweaked to disable one filter?

    https://www.contextures.com/pivottab...datamodel.html

  3. #3
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    I have also come across this piece of code which does exactly what I want, however, there's a flaw as you can see other departments data if you simply write the department name in the disabled cell.

    https://www.myonlinetraininghub.com/...able-filtering

    Is there any way around this?

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    There are several things you can do:
    Do not have the source data of the pivot in the file you send.

    Do not have the source data of the pivot, even if it's in another file, reachable by the recipients (when the recipent opens the file he may well get a security notice that external data connections have been disabled but offers the choise to enable them, which may mean he can gain access to the data file, especially if it's on a network).
    [The user will still be able to see the name of the file it wants to connect to if he tries to change the data source of the pivot table, but he may not see its path. And you can't delete it.]

    One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).

    Most important:
    Un check the option in Pivot Table Options… on the Data tab, Save source data with file,

    and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.

    The safest way is to eliminate the pivot table altogether; copy and paste its values only.

    Oh, and if your pivot is based on the data model, make sure there is no data model in the workbook you send.

    Send a pdf file to them!!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by p45cal View Post
    There are several things you can do:
    Do not have the source data of the pivot in the file you send.

    Do not have the source data of the pivot, even if it's in another file, reachable by the recipients (when the recipent opens the file he may well get a security notice that external data connections have been disabled but offers the choise to enable them, which may mean he can gain access to the data file, especially if it's on a network).
    [The user will still be able to see the name of the file it wants to connect to if he tries to change the data source of the pivot table, but he may not see its path. And you can't delete it.]

    One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).

    Most important:
    Un check the option in Pivot Table Options… on the Data tab, Save source data with file,

    and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.

    The safest way is to eliminate the pivot table altogether; copy and paste its values only.

    Oh, and if your pivot is based on the data model, make sure there is no data model in the workbook you send.

    Send a pdf file to them!!
    That's very interesting and definitely ideas I will take on board in the future. The problem is the source data has to be in the file. With this in mind, is there no way of locking/disabling one filter in a pivot?

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Quote Originally Posted by Ray707 View Post
    The problem is the source data has to be in the file.
    Then your cause is lost.
    Anyone with a little nous can do some internet searching and break any protections on the workbook, then they can create their own pivot from the data.
    Can't you restrict the data to just the relevant departments? I realise this might mean some measures or calculated items/fields might not show correctly if they use company-wide data.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by p45cal View Post
    Then your cause is lost.
    Anyone with a little nous can do some internet searching and break any protections on the workbook, then they can create their own pivot from the data.
    Can't you restrict the data to just the relevant departments? I realise this might mean some measures or calculated items/fields might not show correctly if they use company-wide data.
    Interesting. So when there is source data in the file there is no way around this? Damnnit

    I'll delve deeper into some of your solutions outlined above. Thanks for your help!

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Quote Originally Posted by Ray707 View Post
    Interesting. So when there is source data in the file there is no way around this? Damnnit

    I'll delve deeper into some of your solutions outlined above. Thanks for your help!
    What I've done in the past was to use a macro to write each page field's (e.g. Department) filtered pivot table data to a separate workbook as just their data.

    They lose PT features, but they only get their data.

    You could get fancy and extract their potion of the data from the source data into a worksheet in a new workbook, add a sheet to that WB, and then put a PT using just that data onto their second sheet
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by Paul_Hossler View Post
    What I've done in the past was to use a macro to write each page field's (e.g. Department) filtered pivot table data to a separate workbook as just their data.
    Actually I'd be interested in that- do you have the code for it?

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    I can look for it or recreate it easily

    Give me a little time
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by Paul_Hossler View Post
    I can look for it or recreate it easily

    Give me a little time
    Sure, no problem!

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    I had done something similar here awhile ago, but couldn't find the previous post. It was easy enough to just recreate it for you

    Look at Sales All.xlsm with some made up data

    It makes 3 extract WBs based on Dept page field

    The extract sheet can have some formatting before saving, and you can change the file naming easily

    No real error checking

    Edit: I was a little terse with my variable names, so if that's a problem let me know and I'll expand
    Attached Files Attached Files
    Last edited by Paul_Hossler; 04-06-2021 at 09:32 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by Paul_Hossler View Post
    I had done something similar here awhile ago, but couldn't find the previous post. It was easy enough to just recreate it for you

    Look at Sales All.xlsm with some made up data

    It makes 3 extract WBs based on Dept page field

    The extract sheet can have some formatting before saving, and you can change the file naming easily

    No real error checking

    Edit: I was a little terse with my variable names, so if that's a problem let me know and I'll expand
    Hi Paul, thanks for this. I have a question as it doesn't appear to be working; when I click on 'split pivot table' in the sales all depts file it activates a pop up which says 'done' but no new wb's are created, should I expect the workbooks to pop up in separate windows or does it save the workbooks somewhere..?

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    They should be in the same folder as the 'Master' or the all date WB with a name like

    C:\Users\Ray\My Documents\Sales\Sales2020-2021-04-07-East

    so if the 'All Data' WB is "C:\Users\Ray\My Documents\Sales\Sales2020.xlsm"

    the WB for the East department generated on April 7, 2021 would be


    "C:\Users\Ray\My Documents\Sales\Sales2020-2021-04-07-East"

    where

    wb.Path = "C:\Users\Ray\My Documents\Sales"

    Application.PathSeparator =""

    Left(wb.Name, InStr(wb.Name, ".") - 1) = "Sales2020"

    "-" & Format(Now, "yyyy-mm-dd") = -2021-04-07

    "-" & pi.Value = -East

            'make file name
            sFilename = wb.Path & Application.PathSeparator & Left(wb.Name, InStr(wb.Name, ".") - 1) & "-" & Format(Now, "yyyy-mm-dd") & "-" & pi.Value
    I prefer to keep generated WBs in the same folder as the source, but you don't have to

    I prefer to tag generated WBs with a timestamp and maybe a identifying tag
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    In the attached is a tweak of Paul's macro which will retain some functionality of the pivot table by including a subset of the Sales data pertaining to only the one department in each of the produced files.
    It automates this:
    Quote Originally Posted by p45cal View Post
    One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).
    There are some comments in the code.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  16. #16
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    yep I got it working, thanks!

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    @p45cal

    One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).
    Good tip -- In my suggestion, I was thinking more alone the lines of a static management report, very non-interactive but formatted pretty
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    @p45cal --

    I wanted to see how you got the PT's source data onto a new worksheet in the new workbook

    This seems to be where the magic happens, but the online help for .ShowDetail doesn't talk to it


              With NewPT.DataBodyRange
                .Cells(.Rows.Count, .Columns.Count).ShowDetail = True 'create a reduced data source for the pivot
              End With

    Can you explain a little, please?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Since as long as I can remember you've been able to double-click on a value in the Values area of a pivot table and it creates a new sheet showing the data that's used to make up that value. It applies to grand- and sub-totals too.
    Record a macro of you double-clicking such a value and you should get the Showdetail code.
    To get all the records pertaining to a particular filtered pivot you need to have a grand total to click on, one where both the columns and rows are totalled. The cell containing that grand-total is usually in the bottom right corner of the values area of the pivot, given by pivottable.databodyrange.Cells(pivottable.databodyrange.Rows.Count, pivottable.databodyrange.Columns.Count).

    As mentioned in msg#4 you can turn this ability off:
    Quote Originally Posted by p45cal View Post
    and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.
    Last edited by p45cal; 04-09-2021 at 02:36 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  20. #20
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    Ahh -- I know (also for many years) about the double click on a PT cell, but

    1) always used it interactively

    2) never tried it on Grand Total

    3) Didn't realize that it was .ShowDetails = True that actually added the data WS with the sources of the numbers, esp. since the online help is a little terse

    https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue


    BTW, I also just learned that if you double click on a row or column header (e.g. A5) you get a "Which one" dialog, which rearranges the PT and sets the .InnerDetail property, but no new WS
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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