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.

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

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

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

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

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