What version of Excel are you using? If it's Excel 2007, try changing
this line:
Case "DATA"
to this:
Case "VALUES", "DATA"
cossie wrote:
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" wrote:
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 wrote:
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" wrote:
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 wrote:
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
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com
--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com
--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog:
http://blog.contextures.com