How can I allow users to update a pivot table when it is locked?
I have a work book which has a pivot table integrated as a summary page. I
need to allow the end user to update the pivot table with their changes but not allow them to be able to change/modify/update the pivot table it self? |
How can I allow users to update a pivot table when it is locked?
you can record a macro as you unprotect the sheet, refresh, then reprotect.
Then, add a "Refresh" button to the worksheet, and assign that macro to the button. Rob Coldwell wrote: I have a work book which has a pivot table integrated as a summary page. I need to allow the end user to update the pivot table with their changes but not allow them to be able to change/modify/update the pivot table it self? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
How can I allow users to update a pivot table when it is locke
Debra,
Thanks for the quick reply. I created this macro but the challenge now is that it asks the user to enter the password. My end user cannot know the password to the workbook. Any other ideas? Also, Do you know how to set a print setting to enter the date when the pivot table was last updated. This would be very helpful. Thanks - Rob "Debra Dalgleish" wrote: you can record a macro as you unprotect the sheet, refresh, then reprotect. Then, add a "Refresh" button to the worksheet, and assign that macro to the button. Rob Coldwell wrote: I have a work book which has a pivot table integrated as a summary page. I need to allow the end user to update the pivot table with their changes but not allow them to be able to change/modify/update the pivot table it self? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
How can I allow users to update a pivot table when it is locke
In the recorded code, you can add a password, e.g.:
'========================== Sub RefreshPivot() ActiveSheet.Unprotect Password:="MyPwd" ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh ActiveSheet.Protect Password:="MyPwd", _ DrawingObjects:=True, Contents:=True, _ Scenarios:=True, AllowUsingPivotTables:=True End Sub '======================= To add the refresh date to the footer, you can use a macro. '========================== Sub PrintPivot() Dim ws As Worksheet Set ws = ActiveSheet ws.PageSetup.RightFooter = "Last refreshed: " & _ Format(ws.PivotTables(1).RefreshDate, "dd-mmm-yyyy hh:mm") ws.PrintOut Preview:=True End Sub '========================== Rob Coldwell wrote: Debra, Thanks for the quick reply. I created this macro but the challenge now is that it asks the user to enter the password. My end user cannot know the password to the workbook. Any other ideas? Also, Do you know how to set a print setting to enter the date when the pivot table was last updated. This would be very helpful. Thanks - Rob "Debra Dalgleish" wrote: you can record a macro as you unprotect the sheet, refresh, then reprotect. Then, add a "Refresh" button to the worksheet, and assign that macro to the button. Rob Coldwell wrote: I have a work book which has a pivot table integrated as a summary page. I need to allow the end user to update the pivot table with their changes but not allow them to be able to change/modify/update the pivot table it self? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 07:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com