none
Excel 2010: can I refresh pivot table on protected sheet

    Question

  • 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?

    Thanks,   Jim

    PS:  Originally posted in answers.microsoft.com but told to post to Excel IT Pro Discussion forum instead.


    Jim Holstein

    Thursday, December 20, 2012 3:25 PM

Answers

  • 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

    Sunday, December 23, 2012 1:40 AM

All replies

  • 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:

        ActiveSheet.PivotTables(1).RefreshTable


    Regards, Hans Vogelaar

    Thursday, December 20, 2012 3:37 PM
  • Thanks Hans

    Follow-up Questions:

    1. I'd have to save as XLSM file type, correct?
    2. 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?
    3. 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?
    4. 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.

    Jim


    Jim Holstein

    Thursday, December 20, 2012 4:18 PM
  • 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

    Sunday, December 23, 2012 1:40 AM