Excel 2003 - Pivot Tables & Protection 1

[greengo204]

Currently i am trying to protect a sheet containing a pivot table. I do no wish the user to be able to change the structure of the pivot table and/or be able to edit any cells outside of the pivot table. Although the user should be able to use all the filters in the pivot table to select different fields and be able to refresh the pivot table.

-I uderstand when you protect a sheet that you can allow a user to use pivots, but this option allows them to manipulate the structure.
-Ive tried to select a range that user are allowed to edit (range was all the filters on the pivot table), this did not allow user to use the filters.

I've previously posted in the Microsoft Office forum without a response i was wondering if anyone knows of a VBA solution?

Thanks in advance.

 

[xlbo]

Sorry - can;t think of any way that doesn;t involve shed loads of code...and even saying that, would almost certainly be open to misuse....in short, no clear cut way of doing this - either you let them use the pivot table in the protection with warnings about manipulation of structure or you can;t have pivot table functionality

As an aside, you could of course, create your own drop down lists to mimic the filtering ability of a pivot table and then use sumproduct formulae to return the required results but that would also be pretty complex.....depending on your exact requirements...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[greengo204]

xlbo:

I think i will use your first suggestion of using warning messages,

The reason i need to maintain the structure is that i run a macro on the sheet that require the pivot table to be in a specific arrangement. I was thinking of rebuilding the Pivot table every time the macro ran in VBA but thought this was a bit excessive and would increase the time it took the macro to run, so gave that a miss.

Cheers for confirming that there was no 'easy' way to ensure the structure. I was not sure what options there were.



Crystal Reports 8.5
Microsoft Office 2003
Informix DB

 

[greengo204]

old post but found some useful VBA code thats solved my problems:

http://www.contextures.com/xlPivot12.html