+ Reply to Thread
Results 1 to 6 of 6

Pivot table refresh in a protected sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    04-30-2009
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    9

    Pivot table refresh in a protected sheet

    Any ideas on how I can refresh a pivot table in a protected sheet? Do I have to unprotected the sheet first? If so is there any way around this, as I do not want others adjusting my formulas feeding the pivot tables.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pivot table refresh in a protected sheet

    A couple of methods I've seen and one I've used. Most importantly is setting the "UserInterfaceOnly:=True" flag for protection so that macros are free to effect your sheets while users are not.

    Right-click on the Sheet tab that has your pivot table on it and select VIEW CODE, paste in this code:
    Private Sub Worksheet_Activate() 
        Dim pt As PivotTable 
         
        Me.Protect Password:="Secret", UserInterfaceOnly:=True 
         
        For Each pt In Me.PivotTables 
            pt.RefreshTable 
        Next pt 
    End Sub
    If you have pivottables on multiple sheets, then paste this code into the THISWORKBOOK module instead:
    Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
        Dim pt As  PivotTable, bPiv As Boolean 
         
        If Sh.Type <> xlWorksheet Then Exit Sub 
         
        For Each pt In Me.PivotTables 
            Me.Protect  Password:="Secret", UserInterfaceOnly:=True 
            pt.RefreshTable 
        Next pt 
         
    End Sub
    (source material)
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-30-2009
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pivot table refresh in a protected sheet

    Thanks for the reply. I am pretty good with excel but am a VB novice. Can you walk me though how to enter this data?

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pivot table refresh in a protected sheet

    These are WORKSHEET macros, they go in the worksheet module itself.

    Right-click on the sheet tab and select VIEW CODE.
    The VBEditor will open and the SHEET MODULE will already be open. Paste in the code. Be sure to correct the "password" in the code to your password, or remove that portion (leave in the comma).

    Press Alt-Q to close the editor and save your sheet. Now anytime you select that sheet, the pivottables on that sheet will all refresh.

  5. #5
    Registered User
    Join Date
    04-30-2009
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pivot table refresh in a protected sheet

    thanks!!! this works great. Any idea on an add that would refresh the pivots as the source data changes instead of reselecting the sheet?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pivot table refresh in a protected sheet

    You could experiment with putting that code in a Worksheet_Calculate() event instead.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1