Pivot filter on Protected Sheet Macro

[spycein]

Hi Everybody, i was trying to use pivot filter on a protected sheet. But failed to do to that.

I want to protect the sheet so that no one can alter the fields but use the filter option to view the data.

Thank you in advance.

 

[wesimmo]

It's an option when you protect the sheet, right near the bottom of the list "Use PivotTable reports", just tick that and it should do the job.

 

[spycein]

Hi wesimmo, there is no such option as "Use PivotTable reports" in Ms Excel 2013...
but There is a option called "Use Pivot table & Pivot Chart" and if i tick that option, the pivot table can be altered even the sheet is in protected mode.

 

[wesimmo]

Cool, glad you found it.

 

[spycein]

Hi Wesimmo, i dont need to use "pivot Table & Chart" option.. i need to use pivot report in protected mode.. could you please help me out.

Thanks....

 

[wesimmo]

I'm not really sure what you're trying to do? What do you mean by Pivot Report vs a Pivot Table?

 

[spycein]

if i tick pivot table option during protecting the excel sheet then pivot fields can be altered on protected sheet.. which i dont want... i need to allow only the filter function of pivot table on protected sheet.

 

[wesimmo]

Ah ok, I'm afraid I don't know any way to limit what can be done to the Pivot table, as far as I know you either give them access to make any changes they want or lock them out completely.