Remove ability to filter Pivot Table

I have some code that enables me to remove a lot of the pivot table functionality and it works fairly well, but I also want it to remove the ability to filter on any column. My goal is to basically keep the pivot table formatting and data without allowing any manipulation of the data. I've tried all of the copy/paste special methods I've found online, but none of them truly keep the original formatting.

Here is the code I use to remove certain user abilities:

Sub AllowPivotTable()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
With pt
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False 'Excel 2002+
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
End With
End Sub

Can I modify this to essentially remove every user ability, so that they can only see the data? For now, I think removing the ability to filter is the most important.

I'm looking at using the Protect Sheet function and just do it that way. My only concern is that the user can copy and paste the sheet elsewhere and then have access to the data? I was able to open a blank workbook and copy the sheet to it then I had total control over the pivot table. I will test it with other people in my department to see if it's the same.

My requirements have changed a bit since the last post. Now, I would like for users to be able to use Slicers on the pivot table - and possibly expand/collapse - but have no access to anything else. Is this possible?

Thanks.

I have also read your related post http://www.excelforum.com/excel-gene...le-access.html.

What are your concerns?
- are you concerned about the security of the data?
(ie it must not be capable of being copied)

- are you concerned that users having access to the data may create their own pivot tables that are incomplete/inaccurate
(ie the data is complicated and multifaceted and it is all too easy for the user to make a mistakes when filtering, resulting in "misleading" reports)

- are you concerned that users are likely to"damage" the pivot table itself or the data or both?
(ie - the integrity of the workbook, pivot tables, underlying data, formula etc is at stake)

Originally Posted by Kevin#

I have also read your related post http://www.excelforum.com/excel-gene...le-access.html.

What are your concerns?
- are you concerned about the security of the data?
(ie it must not be capable of being copied)

- are you concerned that users having access to the data may create their own pivot tables that are incomplete/inaccurate
(ie the data is complicated and multifaceted and it is all too easy for the user to make a mistakes when filtering, resulting in "misleading" reports)

- are you concerned that users are likely to"damage" the pivot table itself or the data or both?
(ie - the integrity of the workbook, pivot tables, underlying data, formula etc is at stake)

Kevin - I am concerned with everything you mentioned. Security being less important. Once they have the data they can show it to anyone they like, but I do not want them to be able to manipulate what I send them in the Pivot Table. If I had to choose between allowing them to copy the pivot table and implementing my other requirements, I would choose to allow them to copy the data.

Ideally, I would have one master Pivot table containing all of the data. I would then filter it for the relevant user, and send them the result. They would be able to then filter and expand/collapse, but they would not have access to the underlying data for the other users. Alternatively, I would create a separate Workbook for each user that only contains their data. this would alleviate the concern about viewing other users' data. They would still be able to filter with slicers and expand/collapse, but they could not alter the fields, change calculated fields, or otherwise change the data in ANY way.

This may be difficult to visualize without an example workbook, but I'm trying to remain theoretical for now if possible.

I am happy to discuss it all theoretically, for the moment - I probably should not be trusted with your data either LOL
I will deal with all your concerns separately and give you suggestions to tackle each one, after gaining an understanding of scale.

Let's always strike a sensible balance between control and practicality:
Must make a realistic assessment of the time\effort required to "button things down" measured against the risk if that is not done.
And let's avoid users ending up so hamstrung (and possibly annoyed that they are not trusted) that they go back to the source (ie you) with endless minor requests - which would be a real pain.

Firstly let's consider (quickly, I hope) creating and distributing individual reports:
you have Managers1,2,3 etc who each have a number of staff A,B,C,D,E ...Z reporting to them

Q1 - Assuming that you could easily create individually tailored reports, how many "individual" variations of each report would there need to be? 10+, 50+, 100+
Q2 Would some of these reports be grouped reports for managers? eg Manager1 gets consolidated table ( = Staff A+B+C+D+ E) Manager2 gets a similar report (=staff F+G+H+I +J + some extra data) etc
Q3 How many different types of report are there?
Q4 How often are they produced? Weekly? Monthly?
Q5 Would it be practical/sensible to deal with all those spreadsheets?

Originally Posted by Kevin#

I am happy to discuss it all theoretically, for the moment - I probably should not be trusted with your data either LOL
I will deal with all your concerns separately and give you suggestions to tackle each one, after gaining an understanding of scale.

Let's always strike a sensible balance between control and practicality:
Must make a realistic assessment of the time\effort required to "button things down" measured against the risk if that is not done.
And let's avoid users ending up so hamstrung (and possibly annoyed that they are not trusted) that they go back to the source (ie you) with endless minor requests - which would be a real pain.

Firstly let's consider (quickly, I hope) creating and distributing individual reports:
you have Managers1,2,3 etc who each have a number of staff A,B,C,D,E ...Z reporting to them

Q1 - Assuming that you could easily create individually tailored reports, how many "individual" variations of each report would there need to be? 10+, 50+, 100+
One for each Manager - ~15
Q2 Would some of these reports be grouped reports for managers? eg Manager1 gets consolidated table ( = Staff A+B+C+D+ E) Manager2 gets a similar report (=staff F+G+H+I +J + some extra data) etc
Each Manager would only see their staff members. All else equal between managers. Each Manager should be able to view an individual staff, a group of staff, total for all staff, or any other combo therein.
Q3 How many different types of report are there?
For the moment, one report, but we all know that won't last. Automating the creation and distribution of the reports in Excel would be ideal.
Q4 How often are they produced? Weekly? Monthly?
Monthly
Q5 Would it be practical/sensible to deal with all those spreadsheets?
Unfortunately, my decisions can't always be guided by practicality...

Have a look at my answers above. As you can see for question 5, whether or not this request is practical does not necessarily matter at the moment.

Thanks,
the numbers small enough to make individual reports easily manageable, if other ways of concealing the data do not appeal (we will get onto that soon, I promise...)

To close out the "individual report" discussion:
Q6 If you were to create ~15 individual reports would there be any overlap or unallocated gap in the "total"
(In other words does Rpt1 + Rpt2 + Rpt3 ...................................Rpt15 = PivotTableTotal)
Q7 How stable are the Manager's staff groupings?
(do staff hop between Managers (or Managers hop between staff groups), and if so does their (historic) data follow them)
Q8 Why do Managers only see their own staff's data? Confidentiality or to reduce distractions?
Q9 How would you make the reports available?
- allow managers to run "their" report off your intranet OR
- email separate monthly workbooks to managers

Wow, I feel like I'm working with a paid consultant ! I didn't bother using colored fonts for my responses this time. Too complicated.

Originally Posted by Kevin#

Thanks,
the numbers small enough to make individual reports easily manageable, if other ways of concealing the data do not appeal (we will get onto that soon, I promise...)

To close out the "individual report" discussion:
Q6 If you were to create ~15 individual reports would there be any overlap or unallocated gap in the "total"
(In other words does Rpt1 + Rpt2 + Rpt3 ...................................Rpt15 = PivotTableTotal)
Nothing would be unallocated.
Q7 How stable are the Manager's staff groupings?
(do staff hop between Managers (or Managers hop between staff groups), and if so does their (historic) data follow them)
They should be static, but there are instances where that could change. The historic data stays with the Manager.
Q8 Why do Managers only see their own staff's data? Confidentiality or to reduce distractions?
Confidentiality
Q9 How would you make the reports available?
- allow managers to run "their" report off your intranet OR
- email separate monthly workbooks to managers
For now, it's looking like a monthly email solution.

Final (one big) question (for the moment) - I want a feel for the volume of data

It makes no difference what's contained in the data, just want to appreciate what will happen if we "split the atom"

Q10 Is all master data contained in one single long worksheet? (I presume it must be)
Q11 Is there a separate line for each month for each strand of data?
Q12 How many months' data is to be provided to each Manager in his Pivot Report?
Q13 Is there more than one strand of data for each employee (eg separate products, or split areas etc) - if so how many?
Q14 Are you tracking actuals only, or are there budgets/forecasts included?
Q15 Roughly how many columns and rows of data are there in the master table?
Q16 Is the data growing or are you looking at roughly the same amount of cumulative data each month? (ie moving the view along a month at a time)
Q17 What is the FileSize of that (master data) workbook now?

From everything you've said so far, the simplest solution could well be to create separate monthy worksheets for each manager. But (and this is dependant on the size of the data table) we may be able to keep everything in one file and not let everyone see everything.

Before deciding anything let's consider what else we could do to restrict access to some of the data by examining (in post#11) some of the excellent access controls available within Excel.