RE: Login System
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...
"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?
Re: Login System
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
- 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!
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...
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1