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 wrote:
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


Steve wrote:

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