Prevent pivot table filtering|VBA & Macros|Excel Forum|My Online Training Hub

Hi,

I have a pivot report that gets emailed out to different sales teams. I want to filter the report for a specific team and then lock the report before mailing so that the recipients can only see their specific information. I also want them to be able to maintain full pivot table functionality but just with their data.

I thought I had found a solution whereby I removed the ability to filter on the Team field in the report (see attached example file). The VBA in this file is as follows:

The Disable_Selection code removes the selection from the Team field (Ctrl+D runs)

Sub Disable_Selection()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Team")
pf.EnableItemSelection = False
End Sub

The Enable_Selection code adds back the selection from the Team field (Ctrl+E runs)

Sub Enable_Selection()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Team")
pf.EnableItemSelection = True
End Sub

If you look at the attached file it is filtered for team 03 and you can't select any other teams from the report filter in cell B1. However I have found a flaw, if you type 02 in cell B1 and press enter you will see that the data updates and you now see the information for team 02.

I have thought about protecting the worksheet but it becomes complicated as the users need to have the ability to access the field lists to add additional fields if required and to re-arrange the pivot layout by moving fields. Even if protect the sheet and select the Use PivotTable Reports option it doesn't give you the required functionality. 

The attached file is just an example and not the actual report. In the actual report their are many more teams, many more data fields, and some recipients will get data for multiple selected teams.

Any ideas gratefully received.

Regards

Bax