Pivot Tables don't refresh in locked sheet

[MagnusNovak]

Hi everyone,

I'm having some issues trying to refresh a pivot table (via VBA) in a locked sheet. When locking all the sheets (also via VBA), I make sure to have the statements "UserInterfaceOnly:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True" . But when I try to refresh the pivot tables, I still get the following error message (the same on all sheets with a pivot):

Please Login or Register  to view this content.

To solve this issue, I tried unsharring the data cache of each pivot table by following this tutorial:
https://support.microsoft.com/en-us/...7-9e3a4a05542b

I know I was able to do so because when I run ?ActiveWorkbook.PivotCaches.Count, I do get 4 (the number of pivot tables in my workbook) instead of 1 before. However, I still get the same error message. Also, when I test with only one tab (i.e. all other tabs in the workbook are unlocked, except one that I'm testing), I still get the same error message.

Any idea what else I need to do to solve the issue? Obviously I would like to avoid having to unlock any sheet (even with VBA) because I believeI should be able to refresh pivot in the background without unlocking anything.

Thanks!

[Artik]

Just because you protect the sheet with the parameter UserInterfaceOnly=True, it still does not mean that with the code "with impunity" you can do anything. Unfortunately, if you refresh a pivot table in a given sheet, you have to unprotect the sheet before and protect it after the refresh. This is one of the few exceptions when you have to unprotect the sheet before performing a certain action.

Artik