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.

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

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

"Debra Dalgleish" wrote:

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

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

"Debra Dalgleish" wrote:

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

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 wrote:
Debra,
Please could you advise how I could protect the pivot table.
Thanks,
Teik

"Debra Dalgleish" wrote:


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 wrote:

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
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I will give it a shot.
Thank you.

"Debra Dalgleish" wrote:

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 wrote:
Debra,
Please could you advise how I could protect the pivot table.
Thanks,
Teik

"Debra Dalgleish" wrote:


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 wrote:

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
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html