MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table and sheet Protection problem

Posted by Nona Sloven on May 02, 2001 7:50 AM


I have a pivot table in Excel’97. I wonder if there is a way to protect the sheet where I have put my pivot table to reside. When I choose Tools|Protection|Protect Sheet…, the dropdown lists on my table stops to work. They can’t be opened, and when you hover the mouse cursor over any of them the cursor changes its appearance unto a hand. The reason I need this sheet protection is because some cells I have added with formulas in range B6:C8. I´ll apreciate any help on this. Thank you.

Posted by Mark W. on May 02, 2001 7:56 AM

Nona, are this formulas that you've added somehow
related to your PivotTable; and thus, preventing
you from placing the PivotTable on a worksheet of
its own?

Posted by Nona Sloven on May 02, 2001 8:51 AM


I Just added some rows above my pivot table in the same sheet. The formulas make reference to another cells in the same sheet where I made my pivot table but they don't reference any cell in the pivot table region itself. The cells with the formulas are linked with another cells in another worksheets in the same workbook. The reason for these links is for me to be able to add data to the columns on the sheets that feed my pivot table, in order to make possible to add the data from the sheet of the pivot table, which is the only sheet that will be unhidden. Once the pivot table is built, the user should not be able to see the data on the worksheets except the one in which the pivot table resides. Thank you.

Posted by Dave Hawley on May 03, 2001 1:43 AM

Hi Nona

the way i have overcome this in the past it to make it so the sheet unprotects itself only when the user hase select a certain cell. You need to place this code in the Worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Me.Unprotect password:="secret"
Me.Protect password:="secret"
End If
End Sub

OzGrid Business Applications

Posted by Nona Sloven on May 03, 2001 7:32 AM

I cannot understand your code this time Dave

Hi Dave:

Could you please be more specific?
The user is supposed not to have access to certain cells because they have formulas that I need to protect. If I unprotect the sheet when I select any cell, doesn't it mean any user can have access to the formulas? How can I define a specific range in your code from which the macro is activated. Also, I pasted your code in the worksheet module (I already have a Private Sub Worksheet_Change macro in this module, by the way, a previous code you authored). I need a better understanding of this new code. Your help will be very appreciated. Thank you.