Lock or Protect Pivot Table Layout

[FiservEFTSalesOps]

I'm trying to "lock-down" a pivot table so the users can still drill down, but cannot drag fields and generally mess-up the layout.

I'm creating the tables in Excel 2007, but most users are still using Excel 2003.

Does anyome have a bit of VB code I can save as a macro?

Thanks. This is my 1st post here.

 

[gpsurf]

I am trying to do the same thing and came across the following code. This code does work but I need to see if anyone can help enhance it to accomplish what I am looking to do.

First I have multiple pivot tables in a work book and I would like to have the code run automatically when they open the workbook and restrict them from changing any of the pivot tables.

Second the code below does not lock the row fields from being moved and I would like to lock them as well.

The only thing I want the user to be able to do is use the filters and have the ability to use the drill down function. Also, the Pivot Table needs to be able to be refreshed.

Thanks
George




Sub AllowPivotTable()
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
With pt
.EnableWizard = True
.EnableDrilldown = True
.EnableFieldList = True 'Excel 2002+
.EnableFieldDialog = True
.PivotCache.EnableRefresh = True
For Each pf In pt.PivotFields
With pf
.DragToPage = True
.DragToRow = True
.DragToColumn = True
.DragToData = True
.DragToHide = True
End With
Next pf
End With
End Sub

 

[gpsurf]

I am new to VBA, I just finished the Mr. Excel DVD on VBA but it is still confusing. I have spent the morning going through books and the internet trying to get this code to work the way I want but with no luck. Could one of you VBA gurus lend me a hand?
I have a workbook that has two pivot tables on separate two sheets – Sheet one is “By Account” and the other is “By Vendor”. These sheets are tied to a data sheet that is updated when the workbook is opened; the Pivot tables are also updated on opened. What I have been trying to do is have this code run when I open the work book. I tried a number of variations using – Private Sub Woorkbook_open but I couldn’t get it to work.
What I need to do is have the code run on open and run on both pivot tables.
Any help would be greatly appreciated.
GP

 

[gpsurf]

Any Ideas?

 

[bdfagoodfella]

In excel 2003 you can right click the pivot table and deselect the options:
"Save data with table layout"
"Enable drill to details"

Save, close, and reopen the workbook and you will find it difficult to do anything other than refresh the table.

 

[Mattro78]

Try this one!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Sub RestrictPivotTable()<o></o>
Dim pf As PivotField<o></o>
With ActiveSheet.PivotTables(1)<o></o>
.EnableWizard = False<o></o>
.EnableDrilldown = True<o></o>
.EnableFieldList = True<o></o>
.EnableFieldDialog = False<o></o>
.PivotCache.EnableRefresh = False<o></o>
For Each pf In .PageFields<o></o>
With pf<o></o>
.DragToPage = False<o></o>
.DragToRow = False<o></o>
.DragToColumn = False<o></o>
.DragToData = False<o></o>
.DragToHide = False<o></o>
End With<o></o>
Next pf<o></o>
End With<o></o>
End Sub<o></o>

This macro enables drill down and fieldlist but stops anyone from accessing the actual Pivot field list for drag/drop....essentially allowing functionality while restricting access to any sensitive data you may want not wish to publish.

 

[Fravo]

one word: perfect! just what i was looking for.

 

[MPSpyder]

This works so great that I had to join MrExcel just to reply. Now how in the world to you reverse the macro to show the filed list??
Thanks.

 

[MPSpyder]

Sorry, I'm new to this too.
I replied to your post and asked you how to reverse the macro.
After playing with the code I found AN answer, probably not the best one but it works. And of course I was too late to edit my previous post.
In any case, I appreciate your help. I am posting my version of how to SHOW the fieldlist:
Sub ShowPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
.EnableWizard = True
'.EnableDrilldown = True
'.EnableFieldList = True
'.EnableFieldDialog = False
'.PivotCache.EnableRefresh = False
'For Each pf In .PageFields
'With pf
'.DragToPage = False
'.DragToRow = False
'.DragToColumn = False
'.DragToData = False
'.DragToHide = False
'End With
'Next pf
End With
End Sub


Thanks again.