how to partially protect a pivot table based worksheet


C

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.
 
Ad

Advertisements

D

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
 
C

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
 
D

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
'==================
 
C

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
 
D

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"
 
Ad

Advertisements

C

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
 
D

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?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top