protect a pivot sheet

[Kevin]

I'd like to protect a pivot sheet so that users can't change format,
calculations or anything. However, I would like for them to be able to
click the ! to refresh the data.

If it's possible, it's probably something so obvious that I overlooked it.
I think I've tried every option on the "protect" dialogue box.

Thanks for any tips
Kevin

--

 

[Dave Peterson]

I think you can protect it via code.

This was based on a Debra Dalgleish post:
http://groups.google.com/[email protected]



Option Explicit
Sub PTSettings()
Dim pf As PivotField
With Worksheets("sheet2").PivotTables(1)
.EnableWizard = False
.EnableDrilldown = False
.PivotCache.EnableRefresh = False
For Each pf In .PivotFields
If pf.Name <> "Data" Then
pf.DragToPage = False
pf.DragToRow = False
pf.DragToColumn = False
pf.DragToData = False
pf.DragToHide = False
End If
Next pf
End With
End Sub

(I added the check for the Data field and changed a True to False.)

Man, am I tired <bg>.

 

[Dave Hawley]

Hi Kevin

I would use some simple code like below

Sub RefreshProtectedPivotT()
With ActiveSheet
.Protect Password:="Secret", UserInterFaceOnly:=True
.PivotTables("PivotTable1").PivotCache.Refresh
End With
End Sub

To use this, first change "Secret" to your sheets password. Change
"PivotTable1" to the name of your Pivot Table, find this via "Table
Options".

Now add a CommandButton from the "Forms" toolbar and attach it to the
macro.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Kevin]

Thanks Dave and Dave. Looks like code it is