[Solved] Pivot Tables : Refresh on protected sheet

  • My VBA code password protects all sheets every time I open this Spreadsheet.
    But when I try to refresh the data, I get an error "Cannot Edit Pivottable on protected sheet"
    How could I, using VBA,
    1)unlock the sheets
    2)refresh the Povottables
    3)lock the sheets again


    Thanks in advance


  • Try something on these lines:


    Code
    Sub Test () 
    
    
    ActiveSheet.Unprotect "Password" 
        'your pivot table refresh code - record this with the macro recorder on an unprotected sheet
    ActiveSheet.Protect "Password" 
    End Sub


    HTH

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Hi X-Man


    Here is what I often use. Simply attach the macro to a button on the sheet and change the password (or ommit it) and Pivot Table name to suit. No need to unprotect first as the UserInterFaceOnly arguments allows code to run on a protected sheet. It also eliminates the very real chance of your code bugging out and leaving the sheet unprotected.



    Code
    Sub Update()
        With ActiveSheet
             .Protect Password:="Secret", UserInterfaceOnly:=True
             .PivotTables("PivotTable1").PivotCache.Refresh
        End With
    End Sub



    If you have more than one, use this more generic code



    Code
    Sub UpdateAll()
    Dim pt As PivotTable
        With ActiveSheet
             .Protect Password:="Secret", UserInterfaceOnly:=True
             For Each pt In .PivotTables
               pt.RefreshTable
             Next pt
        End With
    End Sub



    **Be careful when/if using DisplayAlerts=False as it's possible you will overwrite some needed data and you cannot use Undo on a macro change!**

  • Thanks so much Iridium and Dave,


    I went for the last code because I have several Pivottables, but they are not only in the "ACTIVESHEET".


    So my macro runs clicking a picture in activesheet. Then copied the code again so it runs the update on sheets2 and 5. Might not look so efficient but is doing a great job.


    Thank you guys once again.




    Sub UpdateAll()
    Dim pt As PivotTable
    With ActiveSheet
    .Protect Password:="Secret", UserInterfaceOnly:=True
    For Each pt In .PivotTables
    pt.RefreshTable
    Next pt
    End With


    With Sheet2
    .Protect Password:="Secret", UserInterfaceOnly:=True
    For Each pt In .PivotTables
    pt.RefreshTable
    Next pt
    End With


    With Sheet5
    .Protect Password:="Secret", UserInterfaceOnly:=True
    For Each pt In .PivotTables
    pt.RefreshTable
    Next pt
    End With


    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!