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

Workbook protection and Pivot table on the fly
 

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

Workbook protection and Pivot table on the fly
 

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

Workbook protection and Pivot table on the fly
 

Rowan
That will do the trick. Thank you.

"Rowan Drummond" wrote in message
...
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

Workbook protection and Pivot table on the fly
 

You're welcome.

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