Pivot Table Change Filter on Protected Sheet

[firstredpepper]

Hi there,

Would anyone know how to allow users to adjust a filter for a pivot table (external data source - OLAP cube) on a protected sheet? When I try to change the filter I get the following error:

That command cannot be performed on a protected sheet because the source for this PivotTable report requires a data refresh.

The protection settings on the sheet allow users to:
-select unlocked and locked cells,
-edit objects,
-edit pivot tables, and
-autofilter.

What am I doing wrong?

Thank you!!!
Dave

 

[JerryAlan]

Hello, did you ever find a resolution to this? I'm having the same issue. Thanks!

 

[firstredpepper]

Hello, did you ever find a resolution to this? I'm having the same issue. Thanks!

Hi Jerry,

Yes, I ended up unprotecting the sheet programatically in vba whenever doing a refresh. Another option is to run a script on opening the workbook which loops though all sheets and protects them with the userinterfaceonly setting to true (so your macros can change stuff on unprotected sheets without issue) - I do remember still having a bit of trouble with userinterfaceonly and refreshing, so your mileage may vary.

Dave

 

[JerryAlan]

Ah, thanks. Those are the solutions I found elsewhere too. My Excel connects to a live Tabular model so it refreshes the data any time you change the pivot filters. Such an odd thing for Excel to not have an option for the pivots to refresh when the sheet is locked.