Pivot Table Change Filter on Protected Sheet

firstredpepper

New Member
Joined
Jul 19, 2014
Messages
14
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JerryAlan

New Member
Joined
Oct 9, 2015
Messages
12
Hello, did you ever find a resolution to this? I'm having the same issue. Thanks!
 

firstredpepper

New Member
Joined
Jul 19, 2014
Messages
14
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

New Member
Joined
Oct 9, 2015
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,856
Messages
5,598,481
Members
414,241
Latest member
JD18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top