Keep 3 workbooks.
The "protection" in Excel is not for security - it is to prevent accidental
alteration of values, labels, formulas and layout/format of the
worksheets/workbooks. It is not intended to act as a security feature to
prevent one person from doing something while someone else can only do
something else - such as viewing various sheets.
This could be done with some Visual Basic coding (Macro), but again, the
level of security would be absolutely minimal.
But if you want to give it a try, here goes. You'll need an extra worksheet
in the front of the workbook (in this code it is named ControlSheet). You
and your employees will enter a password into cell A1 on that sheet to view
the other sheet(s) in the workbook. Clearing the entry in A1 or making a bad
entry in it will hide the 3 employee sheets.
There are two pieces of this - one is code that is attached to the
ControlSheet itself, and the other is associated with the workbook's Open
event. Here is the code for the worksheet - to put it into the correct
location, open the workbook, go to the ControlSheet worksheet and right-click
on the name tab. Choose [View Code] from the list that appears and copy the
code below and paste it into the module presented to you. Change the
passwords as desired. Close the VB Editor. That's half the work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then
Exit Sub
End If
'note that you may change the passwords to suit your needs
'also, you can change the names of the worksheets referenced
'to match the employee sheet names in your workbook.
Select Case Target.Value
Case Is = "BOSS" ' your password
ThisWorkbook.Worksheets("Alice").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Sue").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Loi").Visible = xlSheetVisible
Case Is = "emp1a" 'password assigned to 1st employee
ThisWorkbook.Worksheets("Alice").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Sue").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Loi").Visible = xlSheetVeryHidden
Case Is = "emp2b" 'password assigned to 2nd employee
ThisWorkbook.Worksheets("Alice").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Sue").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Loi").Visible = xlSheetVeryHidden
Case Is = "emp3c" ' password assigned to 3rd employee
ThisWorkbook.Worksheets("Alice").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Sue").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Loi").Visible = xlSheetVisible
Case Else
Target.ClearContents
ThisWorkbook.Worksheets("Alice").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Sue").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Loi").Visible = xlSheetVeryHidden
End Select
End Sub
Now for the 2nd half. This goes into the workbook's even code area. In
Excel 2003, right-click on the Excel icon in the upper left corner of the
Excel window and again choose [View Code] from the list of options presented.
Copy the code below and paste it into the module presented to you and close
the VB Editor. Save the workbook.
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("ControlSheet").Visible = xlSheetVisible
ThisWorkbook.Worksheets("ControlSheet").Range("A1") = ""
ThisWorkbook.Worksheets("Alice").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Sue").Visible = xlSheetVeryHidden
ThisWorkbook.Worksheets("Loi").Visible = xlSheetVeryHidden
End Sub
Anytime the workbook is opened, the individual employee sheets will be made
invisible, so the user must enter their 'password' into A1 on the
ControlSheet to view any of the others.
Hope this helps some.
Chi said:
Hi,
I created "Scheduling†workbook that has 3 worksheets - Alice, Sue, and Loi.
I would like Sue to only see her sheet when she opens the Scheduling
workbook/excel file. The same way for Alice and Loi.
For myself, I would like to have full permission to see all work sheets.
Please help
Thank you!
