Protected Sheet - Allow Pivot Filter, but not Field List

Dear all

I would like to protect my worksheet, while still allowing the user to use filters in Pivot Tables.
However, I do not want to allow them to change the fields.

Is there a way to do this? The default "Protect Sheet" does not offer this option.

Thanks
FD

Originally Posted by FallingDown

I would like to protect my worksheet, while still allowing the user to use filters in Pivot Tables.
However, I do not want to allow them to change the fields.

Is there a way to do this? The default "Protect Sheet" does not offer this option.

1. From the menu bar select Review> Protect Sheet
2. Under Allow all users of this workbook to:

- uncheck: Select locked cells
- uncheck: Select Unlocked cells
- check: Use AutoFilter
- check: Use PivotTable & PivotChart

3. Set & confirm your password

Hi Syrkrasi

Many thanks for your reply. However, as already described in my post, this does NOT work.
Having Pivot Table checked, still allows the user to move around the fields - I do not want that.

Plus the auto-filter has nothing to do with the pivot filters.

Do you see any other approach?

Thanks
FD

Here is what i was able to find

try this code

Please Login or Register  to view this content.

And your right that the Autofilter doesn't do anything for your request; however, you should still use the following:

1. From the menu bar select Review> Protect Sheet
2. Under Allow all users of this workbook to:
- uncheck: Select locked cells
- uncheck: Select Unlocked cells
- check: Use PivotTable & PivotChart
3. Set & confirm your password

See attachment

Hi Syrkrasi

Yeah, I know the basic functions ;-) But thanks appreciated anyway.

I thought of coding this myself, but tried to avoid (more code then I already use).
So I figured I'd check if I can use a built-in function I missed.

Cheers
FD

I am unaware of any other built-in function that will allow you to manually turn-off that setting.
From this standpoint - using VBA seems to be the only solution in achieving your request.

Some users say that the suggestion below does not work:
1. From the menu bar select Review> Protect Sheet
2. Under Allow all users of this workbook to:
- uncheck: Select locked cells
- uncheck: Select Unlocked cells
- check: Use AutoFilter
- check: Use PivotTable & PivotChart
3. Set & confirm your password

It will NOT work if when you protect the sheet you have a cell selected that is within the Pivot Table. Select a cell outside of the Pivot Table before you protect the sheet.