Thursday, December 20, 2012 3:25 PM
I have a workbook as follows
worksheet/tab1 "data": the data in A1-O500 & NOT protected
worksheet/tab2 "Pivot1": Pivot1 is based on A1-O500 & protected
worksheet/tab3 "Pivot2": Pivot2 is based on A1-O500 & protected
On Pivot1 and Pivot2 these Protection boxes are checked:
- Locked cells
- unlocked cells
- allow filters
- allow pivot reports
However when I add data on tab1, I am unable to refresh either pivot table.
I've searched a few forums and the only solutions I find are to manually [or with a macro] unprotect the sheets, then refresh-all, then reprotect the 2 sheets.
Is using manual-or-macro steps to unprotect-refresh-reprotect the only way to refresh pivot tables on protected sheets?
PS: Originally posted in answers.microsoft.com but told to post to Excel IT Pro Discussion forum instead.
Thursday, December 20, 2012 3:37 PM
Normally: yes, you have to unprotect and reprotect the sheet.
However, you can enable pivottables and protect the sheet in VBA with UserInterfaceOnly:=True:
ActiveSheet.EnablePivotTable = True ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
You can then refresh the pivottable in a macro without unprotecting the sheet:
Regards, Hans Vogelaar
Thursday, December 20, 2012 4:18 PM
- I'd have to save as XLSM file type, correct?
- If I do the first 2 code lines you suggest, could the user manually refresh the pivottable with ALT+F5 -or- by clicking refresh on the ribbon?
- I could set "ActiveSheet.EnablePivotTable = True" on sheet2 properties [using ALT+F11 VBA window] correct instead of coding it? If no, where would I put those 2 lines of code?
- Where would I put code "ActiveSheet.PivotTables(1).RefreshTable" so Excel would refresh when user clicked on the tab? Would that go in sheet2 code [VBA window + F7] under worksheet.activate?
As you can tell - I'm very rusty on VBA - having been retired for 2 years and now helping my minister [hospital chaplain] wife with some data analysis for her department.
Thanks again & Merry Christmas, Hans, to you and yours.
Sunday, December 23, 2012 1:40 AM
Sorry, your follow-up questions escaped my attention.
1. You can save as .xlsm, .xlsb or the legacy format .xls, but NOT as .xlsx.
2. No, the user will have to run a macro. You can make the macro available through a custom Quick Access Toolbar and/or a custom keyboard shortcut. Or you can refresh the pivottable when the worksheet is activated - see point 4.
3. You can indeed set EnablePivotTable to True in the Properties pane for the worksheet, but it will not be saved, so it's better to set it using VBA. See the Remark at the end of this reply.
4. Yes, that's a possibility:
Right-click the sheet tab of the sheet containing the pivottable.
Select View Code from the context menu.
Enter or copy/paste the following code into the worksheet module:
Private Sub Worksheet_Activate() Me.PivotTables(1).RefreshTable End Sub
Remark: you have to protect the sheet with UserInterfaceOnly:=True each time the workbook is opened:
Double-click the ThisWorkbook node in the project explorer on the left hand side of the Visual Basic Editor.
Copy/paste the following code into the ThisWorkbook module:
Private Sub Workbook_Open() With Me.Worksheets("Sheet1") .AllowPivotTable = True .Protect Contents:=True, UserInterfaceOnly:=True, _ AllowUsingPivotTables:=True End With End Sub
where Sheet1 is the name of the sheet containing the pivottable.
Regards, Hans Vogelaar
- Marked As Answer by Rex ZhangModerator Friday, December 28, 2012 4:52 AM