How do I prevent access to other page area in pivot table report?

[kedahboy]

I have created a pivot table report in which I have added a field in the Page
Area. This field represents the different departments in the company and I do
not want one department to view the data from another department. As such I
need to protect the Page Area so that the department can only view the data
for that department but must be able to show and hide details.
I find that if I protect the worksheet then one is unable to show and hide
details!
Thanks guys.

 

[Debra Dalgleish]

If you don't want one department to view data from another department,
you should create a separate pivot table for each department, based on
its data only.

You could protect the pivot table, to prevent changes to the page field,
but it's easy to circumvent Excel's worksheet protection.

 

[kedahboy]

Debra,
Thanks for reply.
I guess I am just being lazy and trying to avoid creating a pivot table for
each department with its data only.
If I just have one set of data from which to create the pivot tables it
would be much easier.
Regards

 

[kedahboy]

Debra,
Please could you advise how I could protect the pivot table.
Thanks,
Teik

 

[Debra Dalgleish]

You can use programming to lock the page fields. For example:

Sub DisablePageSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PageFields
pf.EnableItemSelection = False
Next
End Sub

 

[kedahboy]

I will give it a shot.
Thank you.

You can use programming to lock the page fields. For example:

Sub DisablePageSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PageFields
pf.EnableItemSelection = False
Next
End Sub