Pivot Table and sheet Protection problem

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?

Mark:

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.

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"
Else
Me.Protect password:="secret"
End If
End Sub

Dave
OzGrid Business Applications

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.

Nona