Protect sheet, but allow pivot filter changes?

[muzikman69]

I currently have a worksheet which has a pivot running on it; I am trying to set it up so that the user can update the pivot (change filter criteria, refresh, etc) but at the same time certain other cells are protected from editing.

When I protect the sheet (and ensure the pivot area is unlocked) I get the message "Cannot edit PivotTable on protected sheet". Anyone know how to get around this?


Cheers,
Graeme,

 

[Andrew Poulsom]

When protecting the worksheet there is an option to allow the use of PivotTable reports.

 

[muzikman69]

That was simple... should have just looked down the list a bit more eh.

Thank you very much,

Cheers,
Graeme,

 

[biandi]

Hi, I have the same problem but in my case I just want the pivot filter options to be enabled. I do not want the user of my sheet to be able to change anything else from the pivot table. Is there a way to code this so that I just enable pivot filter options. Thanks!

 

[Andrew Poulsom]

I don't know a way to prevent the user from changing the layout of the pivot table, if that's what you mean.

 

[muzikman69]

Here is some VB that I use to disable pivot tables in their entirety, you may be able to tweak it to suit your needs. There is also a link to the original site I got the code from,


Cheers,
Graeme,

           'pivot table tutorial by http://www.contextures.com/xlPivot12.html

                With ActiveSheet.PivotTables(1)
                  .EnableWizard = False
                  .EnableFieldList = False   'Excel 2002+
                            Dim pt As PivotTable
                            Dim pf As PivotField
                            Set pt = ActiveSheet.PivotTables(1)
                              For Each pf In pt.PageFields
                                  pf.EnableItemSelection = False
                              Next pf
                End With