how to partially protect a pivot table based worksheet

[cossie]

Hi, I need to partially protect a pivot table based worksheet but to allow
users to select some of pivot table fields but not others. How can I do this.

I have tried protecting using the lock cells/elements feaure to allow users
to use pivot table reports but then I get a message saying it cannot perform
the command because it needs a data refresh and I need to unlock the
worksheet.

 

[Debra Dalgleish]

You could use programming to restrict one or more of the fields in the
pivot table. For example:

Sub RestrictSingleField()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Employee")

With pf
.EnableItemSelection = False
.DragToHide = False
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
End With

End Sub

 

[cossie]

Hi Debra, you'll have to excuse me, i'm not familiar with programming but I
can kind of understand what you are asking me to do. The pivot cells I dont
want the user to touch are in B1 and B3. The one I want them to able to
change is in B2 which is "posting date" which allows them to select a
day/quarter/month/year.
What would be the code to do this ?

many thanks

Sean

 

[Debra Dalgleish]

This code would lock down all the fields except Posting Date:

'====================
Sub RestrictSpecificFields()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.PivotFields
Select Case UCase(pf.Name)
Case "POSTING DATE"
With pf
.EnableItemSelection = True
.DragToHide = True
.DragToPage = True
.DragToRow = True
.DragToColumn = True
.DragToData = True
End With
Case "DATA"
'do nothing
Case Else
With pf
.EnableItemSelection = False
.DragToHide = False
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
End With
End Select
Next pf

End Sub
'==================

 

[cossie]

Hi Debra, thanks again for this. I have pasted your code into a new sheet
module and ran it and I get "runtime error 1004 application or object defined
error". On debug it stopped at this command .EnableItemSelection = False
Any further help would be appreciated.

thanks

Sean

 

[Debra Dalgleish]

What version of Excel are you using? If it's Excel 2007, try changing
this line:

Case "DATA"

to this:

Case "VALUES", "DATA"

 

[cossie]

Hi Debra,

I have tried adding your code both in Excel 2003 and 2007 (with the slight
change you suggest) and I still get the error 1004 and debugger still stops
at the same line as before.

thanks

Sean

 

[Debra Dalgleish]

When it stops at that line in the code, what field name appears if you
point to "pf" in the line above?
Does the code remove any of the drop down arrows in the pivot table?

 

[cossie]

it says pf="[Posting Date]"

None of the drop down arrows are removed.

Sean