| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to partially protect a pivot table based worksheet
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
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 wrote: 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" wrote: 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 -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to partially protect a pivot table based worksheet
it says pf="[Posting Date]"
None of the drop down arrows are removed. Sean "Debra Dalgleish" wrote: 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 wrote: 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" wrote: 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 -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PROTECT PIVOT TABLE | Excel Discussion (Misc queries) | |||
| Pivot Table Field List Is Only Partially Displayed | Excel Discussion (Misc queries) | |||
| protect parts of a pivot table | Excel Worksheet Functions | |||
| Pivot table with protect workbook | Excel Discussion (Misc queries) | |||
| Partially protect a cell? | Excel Worksheet Functions | |||
Linear Mode
