PDA

View Full Version : Help protecting pivot tables with exceptions



mannlorraine
11-22-2007, 10:58 AM
Hi,

I'm trying to unprotect a pivot table, I have used this code to protect the pivot table..


Sub ProtectPivotTable()
Dim PT As PivotTable
Dim PF As PivotField
Set PT = ActiveSheet.PivotTables(1)
With PT
.EnableWizard = False
.EnableDrilldown = True
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each PF In PT.PivotFields
PF.DragToPage = False
PF.DragToRow = False
PF.DragToColumn = False
PF.DragToData = False
PF.DragToHide = False
Next PF

End With
End Sub


This code works fine.

Now i'm trying to write the reverse code unprotect the pivot table...


Sub ProtectPivotTable()
Dim PT As PivotTable
Dim PF As PivotField
Set PT = ActiveSheet.PivotTables(1)
With PT
.EnableWizard = True
.EnableDrilldown = True
.EnableFieldList = True
.EnableFieldDialog = True
.PivotCache.EnableRefresh = True
For Each PF In PT.PivotFields
PF.DragToPage = True
PF.DragToRow = True
PF.DragToColumn = True
PF.DragToData = True
PF.DragToHide = True
Next PF

End With
End Sub

The code in bold works fine when unprotecting a pivot field however I get an error when it tries to unprotect a calculated field.

The error is:
Run-time error "1004":
Application-defined or object-defined error.

Can anyone please suggest the code to get around this please.

Thanks very much!

Lorraine