Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refresh Pivot table data in protect sheet | Excel Discussion (Misc queries) | |||
Conflicts with autorefresh in pivot table activating sheet protect | Excel Discussion (Misc queries) | |||
Protect Sheet Macro | Excel Worksheet Functions | |||
Protect sheet in macro | Excel Worksheet Functions | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |