N
Nico
Hi,
Like the subject says
)
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/microsoft.public.excel.programming/msg/20e3b986806709b7)
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!
Like the subject says
)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/microsoft.public.excel.programming/msg/20e3b986806709b7)
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!