Macro, how to protect sheet, but allow pivot?
I have an event macro Workbook_Open() that contains:
For Each wSheet In Worksheets wSheet.Protect Password:="xxxxxx", _ UserInterFaceOnly:=True, _ AllowUsingPivotTables:=True, _ AllowSorting:=True Next wSheet This is to protect the worksheets. I have a sheet with a summary pivot table and have assigned a macro to a button to refresh the pivot with ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh This fails due to the protection, I assumed the UserInterFaceOnly:=True would allow the macro to run OK. I get run-time error 1004 and a message "That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data." "To remove protection from the sheet that has the other report....." But there is only one pivot table in the workbook. Any ideas how to run the pivot refrsh macro? Many thanks |
Macro, how to protect sheet, but allow pivot?
Try unprotecting your worksheet (using VBA) just before your refresh code.
Then protecting the sheet again after your refresh code. "David ...M..." wrote in message ... I have an event macro Workbook_Open() that contains: For Each wSheet In Worksheets wSheet.Protect Password:="xxxxxx", _ UserInterFaceOnly:=True, _ AllowUsingPivotTables:=True, _ AllowSorting:=True Next wSheet This is to protect the worksheets. I have a sheet with a summary pivot table and have assigned a macro to a button to refresh the pivot with ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh This fails due to the protection, I assumed the UserInterFaceOnly:=True would allow the macro to run OK. I get run-time error 1004 and a message "That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data." "To remove protection from the sheet that has the other report....." But there is only one pivot table in the workbook. Any ideas how to run the pivot refrsh macro? Many thanks |
All times are GMT +1. The time now is 06:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com