Something like this is no small task. It will require a fair bit of coding. A
couple of hints for you would be to have all but one sheet very hidden. Then
based on who is openeing the book unhide the appropraite sheets. You will
also have to maintain one sheet of user names to provide levels of access. If
you use the windows login name then you can avoid the whole password issue.
Environ("UserName") will return the login name. Before the sheet closes you
will have to rehide all of the sheets. Best of luck...
--
HTH...

Jim Thomlinson


"Kyle Smith" wrote:

Hi there

I want to make a login system so only certain people can access the
worksheet with their own username and password... and people with certain
login levels can access certain sheets/areas etc.

On my database sheet I simple want to be able to add in usernames,
passwords, access levels and the name and then the login system can do the
work with the info.

I also want all the sheets to be hidden and locked so they can't be
un-hidden without certain access HOWEVER does the sheet need to be on sole
use only to do that? (It will be a shared workbook).

I have attached as basic model of what I want my workbook to do... but it
doesn't work - I'm not good with coding!

Can anyone please help me?

Thanks!

Kai

And to add to Jim's comments, it *is* a real pain to hide the sheets. It
isn't sufficient just to hide them on workbook close, you have to trap
every save event (in case the user saves the workbook without closing)
then unhide them so it is seamless to the user.

Furthermore, there is a risk, albeit small, that your sensitive data
will be exposed to a user opening your workbook without macros enabled,
in the event that a user with full access manages to save the workbook
without the event firing (they could be tinkering in VBE, debugging or
something else might have crashed that disabled Application Events).

I prefer to use the following method therefore. It may or may not suit
your purpose:

- Password protect the workbook for read access.
- Create a second workbook or AddIn to programmatically open the
workbook. It could open the main workbook automatically as an
Open event or maybe have a button on a toolbar or worksheet for
the user to select. (I just install an AddIn that places a button
on the command bar.)
- The important thing is, when you program the code to open the
main workbook it should include the following:
- Disable the Cancel key (application.EnableCancelKey =xlDisabled)
so the user can't interrupt the code. Be very careful using this!
- Set application.screenupdating = False
- Now open the workbook. The password can be hardcoded in your
code. Which in turn is password protected in the VBA project.
- Hide/unhide all the sheets as necessary.
- Set application.screenupdating = true
- Enable the Cancel key (application.EnableCancelKey =xlInterrupt)
- You're all set.

I hope this helps you out. And I second Jim's suggestion of using login
name - a lot cleaner for the user (although impractical if people share
a login). I normally control access through an ini file I keep on a
drive which is read only to other users.

Question to Jim:
I've never used Environ("UserName") - nice! I've always used an API call
to get the username. Are there any drawbacks to it? It seems a lot
simpler to use!

Gareth


Jim Thomlinson wrote:
Something like this is no small task. It will require a fair bit of coding. A
couple of hints for you would be to have all but one sheet very hidden. Then
based on who is openeing the book unhide the appropraite sheets. You will
also have to maintain one sheet of user names to provide levels of access. If
you use the windows login name then you can avoid the whole password issue.
Environ("UserName") will return the login name. Before the sheet closes you
will have to rehide all of the sheets. Best of luck...