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.
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.
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:
If you have pivottables on multiple sheets, then paste this code into the THISWORKBOOK module instead:![]()
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
(source material)![]()
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
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon 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!)
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?
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.
thanks!!! this works great. Any idea on an add that would refresh the pivots as the source data changes instead of reselecting the sheet?
You could experiment with putting that code in a Worksheet_Calculate() event instead.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks