Pivot Table limiting user selection

  • Thread starter Thread starter PJS
  • Start date Start date
P

PJS

Hi,

I have a pivot table with different region and different Operational
Directors' performance. I would like to limit the user to their own data
without seeing others' numbers. How would I do that?

For example, let say, there are 3 operational directors A, B and C, if
person A opens the spreadsheet, is it possible for him/her to enter a
password to access his/her info only?

thanks,

PJS
 
While this is possible via VB, to set something up so that certain sheets
are hidden/displayed when the correct password is given, note that this is
by no means secure. If Director A "really" wants to see the other directos
reports, you won't be able to stop them unless the data is physically
seperated. XL is just simply not built to provide that type of secure
functionality.

Further detail:
Many people think that sheet/worksheet protection offers them security. It
doesn't, is simply offers structure/data protection in that sense that you
don't accidentally overwrite it. The classic code to crack those interal
codes can be found here:
http://www.mcgimpsey.com/excel/removepwords.html

Further, the passwords used to open XL, or the code itself, can be cracked
with several 3rd party applications.

In summary, if you're wanting to create different views of data for
convenience, go ahead and put it all in one workbook and set up some custom
passwords and such. If you're looking for security on sensitive data
(salary, performance review, etc) use seperate workbooks.
 
Hi,
you will need to set up a diferent worksheet for each director, then hide
the worksheet, but before hidding it, right click on the mouse view code,
coppy this, the password used is called Manager, change it for yours, there
are three places where the password is mentioned

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
 
Back
Top