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!

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" wrote:

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!

Hi JLatham,

Thank you very much for your help!
I will try it and let you know.

Thank you very much
Chi



"JLatham" wrote:

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" wrote:

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!

Hi,

It works very well! Thank you very much for your help! Your instruction is
very clear and detail.
Thank you very much!

Chi


"JLatham" wrote:

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" wrote:

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!

Hi Jlatham,

I have more questions, please. The code works very well!

I moved the Scheduling file to Share drive so that all of us can access it.
The problem is that Sues excel program has a high security level so that
when she opens the file, the security message appeared. I can change the
security level to MEDIUM or lOW, but it isnt convenience if she uses
different computer. Plus, somehow Sue can see Loi and Alices sheets if she
hit yes or no on the Security message.
Is there a way you can write the code so that everyone can open the file
without seeing the SECURITY message?

Thanks
Chi


"JLatham" wrote:

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" wrote:

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!