Shared Workbook - enble user to see only their own sheet

Posted by Alix on March 05, 2001 9:01 AM

I am creating a time-keeping workbook for a small team in my office. This will run from a single shared workbook in which each user will have their own sheet.

I want each person's data to be hidden from other users and therefore have created a login page where they select a button to unhide their sheet. Unfortunately, this obviously means that they can also unhide anyone else's sheet.

Protecting the individual sheets with passwords does not stop this as I don't need the password to unhide the sheet. So I need a way of preventing a sheet being unhidden unless the correct password is entered.

Many thanks in advance for any help
Alix

Posted by Dave Hawley on March 05, 2001 11:00 AM

Hi Alix

To ensure users cannot see each others pay details you will need quite a bit of code. Some placed in the "Workbook_Decativate" event the "Workbook_BeforeClose" the "Workbook_Open" etc.

All sheets, except your log in page should be xlVeryHidden by these events and you need to account for a user disabling macros. I would not reccomend using the normal hidden propety of the sheets as they will be able to Unhide them via the menu bar.

You will also need to Lock the VBE for viewing so users cannot just push Alt+F11 and see all passwords.

Also there can be problems if a user changes the sheet name, so it is important to use the sheets "CodeName"

You may have already accounted for these, in which case you could apply some code like below to your button on the "Log in Page"

Sub Security()
Dim Reply As String
Reply = InputBox("Enter Password", "Security")
Select Case Reply
Case "carrot"
Sheet1.Visible = True
Case "secret"
Sheet2.Visible = True
Case "keyword"
Sheet3.Visible = True
End Select
End Sub

Dave

OzGrid Business Applications

Posted by Alix on March 06, 2001 3:12 AM

Thanks Dave - that worked brilliantly.
I have even been able to add a messagebox to show when an incorrect password is entered and restart the process so it all runs smoothly. Cheers!

Posted by Alix on March 07, 2001 2:28 AM

Re: Shared Workbook - One last thing...

Hiya
I need one last little thing and my mind has gone blank on it. I want to use PasswordChar to replace typed characters with place holders but what "object" do I apply it to?
Thanks again
Alix

Posted by Celia on March 08, 2001 4:40 PM

Re: Shared Workbook - One last thing...

Alix
I think you can only do this with a User Form TextBox
Celia