+ Reply to Thread
Results 1 to 2 of 2

RE: Login System

  1. #1
    Jim Thomlinson
    Guest

    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...
    --
    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
    >
    >
    >


  2. #2
    Gareth
    Guest

    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
    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...


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1