Protect sheet with PivotTable based on OLAP data
 

Hi,

Like the subject says :o)
I want to protect a sheet on which I have a PivotTable that is based
on OLAP Data.
Also I want to give the enduser the possibility to change the
pagefields. (that's the tricky part)

I added this code:

Private Sub Workbook_Open()

With Sheet1
.Unprotect 'No password for now
.PivotTables(1).PivotCache.Refresh
.Protect Contents:=True, UserInterfaceOnly:=True,
AllowUsingPivotTables:=True
.EnableOutlining = True 'Otherwise Grouping will not work
End With

End sub

But when I change the pagefields the pivottable must connect again to
the source and does give me a nice messagebox:

"That command cannot be performed on a protected sheet because the
source data for this PivotTable report requires a data refresh. To
remove protection... please...again"

The annoying thing is that I can't catch an PivotTable event after the
PivotTable selection change to unprotect and protect again, and
meanwhile do a PivotCache.Refresh.
The Worksheet_PivotTableUpdate occurs after update ;o(

I have an alternative solution (with thx to Dave Peterson:
http://groups.google.be/group/micros...e3b986806709b7)
But i don't like the undo statement ;o), i would have preferred a
Cancel flag ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Me.Range("AGRegionsProtected")
On Error GoTo errHandler:
If Intersect(Target, myRng) Is Nothing Then
'let 'em do it
Else
With Application
.EnableEvents = False
.Undo
End With
MsgBox "I've asked you not to change this range!"
End If
errHandler:
Application.EnableEvents = True
End Sub

An other approach is to buid your own custom MDX browse comboboxes for
replacing the Pagefields...

Thanks for reading and suggestions!