Workbook protection and Pivot table on the fly

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

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

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

Rowan
That will do the trick. Thank you.

"Rowan Drummond" <rowanzsaNotThis@hotmail.com> wrote in message
news:epOfI%23%237FHA.2816@tk2msftngp13.phx.gbl...
> 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
>>>

You're welcome.

Steve wrote:
> Rowan
> That will do the trick. Thank you.