Workbook protection and Pivot table on the fly

[Steve]

Background - I need to protect sheets in a workbook to prevent deletion of
the sheets. In the workbook, I currently have a pivot table. I would like
users to be able to drill-down.

Initial testing shows that drill-down requires an unprotected workbook. I
can deal with this - add code to the sheet to turn off protection when the
pivot sheet is activated, turn it on when deactivated. If done, how do I
stop the pvtSheet being zapped when protection is off? Alternatively, do I
write code to create a new pvtSheet and clean this up together with
drill-down sheets on exit?

The latter has the advantage of prevent users messing with the pivot layout
and saving saved file size. But, how do I deal with Excel's rejection of any
existing pivot table name?

Do I use a set pivot and deal with associated problems or build a fresh
pivot each time? I'm leaning towards the latter but, your comments would be
appreciated.

Steve

 

[Rowan Drummond]

Hi Steve

If you mean drill down by double clicking on a value in the pivot table
you could get around this as follows. Start out with the Pivot Table
created and the workbook protected. Right click the pivot table sheet
and select view code. Add this before double click event:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
ThisWorkbook.Unprotect password:="thepassword"
End Sub

This will unprotect the workbook and allow a new sheet to be created.

Then goto the ThisWorkbook Code module and add the following NewSheet event:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Me.Protect password:="thepassword"
End Sub

This will protect the book again as soon as the newsheet is created.

Hope this helps
Rowan

 

[Rowan Drummond]

And of course that will fail miserably as the workbook will be
unprotected if the user double clicks anywhere outside of the pivot table...

Change the double click event so that the target address (double clicked
cell) is within the pivot table data fields (C5:C11 in my example):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C5:C11")) Is Nothing Then
ThisWorkbook.Unprotect
End If
End Sub

Regards
Rowan

 

[Steve]

Rowan
That will do the trick. Thank you.

And of course that will fail miserably as the workbook will be unprotected
if the user double clicks anywhere outside of the pivot table...

Change the double click event so that the target address (double clicked
cell) is within the pivot table data fields (C5:C11 in my example):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C5:C11")) Is Nothing Then
ThisWorkbook.Unprotect
End If
End Sub

Regards
Rowan

 

[Rowan Drummond]

You're welcome.