Protect the sheet but allow pivot table to be used

[Spyridion]

Hello,

I am trying to protect a sheet such that the options Select Unlocked Cells and Use PivotTable & PivotChart are enabled via VBA.

I tried to use

Please Login or Register  to view this content.

but nothing happens andthe sheet stays unprotected. Is there anyone that can point me to the right direction? Thanks.

[Richard Buttrey]

Hi and welcome to the forum.

Maybe

Please Login or Register  to view this content.

Note it's good practice to use the VBA Sheet Code name rather than the sheet Tab name since they are too easily changed by users and if so your macro wll crash.

[Spyridion]

Hello,

Thanks, glad to be here!

Quick question, is the Sheet Code the sheet number? So if the pivot table is on sheet 2 then it would be With Sheet2 End With?

[Richard Buttrey]

No,

The Sheet VBA Code Name is the name you see in the VBA Project area which lists the sheet objects, the Workbook object and any Modules you might have used.

It will look like

Sheet1(sheet_tab_name)

The code name is the first bit not in parentheses.

[mhovidz]

Hi Spyridion,

There are two name attribute for worksheet

1. Name that is visible and can be adjust by user
2. Name that is visible and adjustable only inside code window

sheetname.JPG

On the above example (please look very closely on highlighted part), I have sheet named "Sheet3", while i change its other name to "wsData".

Either Sheet3 or wsData I can use to code, BUT using sheet3 may fail due to user may change its name, use wsData instead

[Spyridion]

Hello,

Oh, I see. I get it now. Thanks!

Richard Buttrey - I have tried your suggestion of using EnableSelection but I'm locking the entire table now as I am getting a cannot edit PivotTable on Protected Sheet message when I try to click on a slicer. This is greatly confusing me as the Allow using Pivot Tables option is set to true.

[mhovidz]

Try this code

Please Login or Register  to view this content.

[Spyridion]

Thanks mhovidz! That worked. I just had to remove allow filtering.

[mhovidz]

Happy to help