Results 1 to 12 of 12

Thread: Password/Login

  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unhappy Unanswered: Password/Login

    Hey guys, i'm new here, but not that new to Excel. I need to make a password system for my workbook. How would i do this using Macros. Screenies of example code would be great if you could.

    I am also wondering whether i could write a code for a login system aswell. So you must enter a valid Login name and password to enter the workbook (or worksheet) any help would be good!

    Thanks.

    P.s. I've spent about an hour trying to work it out myself!
    Last edited by Lone-Fighter; 10-14-04 at 11:39.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Yes, I have done this. Double entry check, first for a valid name, and if that name is valid, then user password which would only provide specific data for that person. I have to leave for about 2 hours, but will post later today.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    Ok Thanks mate!

  4. #4
    Join Date
    Oct 2004
    Posts
    3
    And is it also possible to put in a list or 2 option boxes for admin or guest?

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    is it also possible to put in a list or 2 option boxes for admin or guest?
    This process is setup using VBA and hidden sheets, one in particular is the Admin sheet which contains all the essential data. Are you familiar with VBA? If you are, you would be able to make adjustments on the Admin Sheet.

    BTW, welcome to the Board!!!
    Last edited by shades; 10-14-04 at 22:01.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Oct 2004
    Posts
    3
    Kool, i'm kinda new to VB! Just started using it in college, got about 6 hours of experience. So i'm not that familiar. Especially with using it inside Excel. Is there any example code i could view?

    And thanks, i'm hoping this Forum will help me with my studies. Maybe sometime i can contribute my experience aswell!

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    The attachment has four worksheets.

    1. Intro is the basic log-in sheet. When you are using this, all the other worksheets should be hidden (ALT + O + H + H).

    2. ShowData will appear if the log-in is correct. The code will only put the appropriate data in column B, for the one who logs in. The table can be filled in using lookup or Index/Match.

    3. Base contains all the complete data. You can update this, and the code will automatically adjust, so that when it is pulled into the ShowData worksheet it will be current.

    4. Admin This worksheet has all the administrative detail. Column A has the list of people who can login, and Column B has the corresponding password. D2 and E2 contain the user information that is typed it. D3 and E3 are the checks against Columns A and B. The code verifies that these match D2 and E2. H1:L1 lists the managers/supervisors. Data under each name indicates which set of data will populate the ShowData worksheet once login is complete.

    Note there are several named ranges, some static, some dynamic. Check those out as well. The VBA uses Select Case for much of the "heavy" work in making this work. If you add another supervisor, say in M1 on Admin (and also in Column A with appropriate password in Column B), then you will need to add another Select Case in the code. You will also have to add a dynamic named range (just use one that is there, give it another name, and adjust the column references in the OFFSET formulas).

    =======================

    To test it, go to ShowData and hit the END button. This will clear the current data, then hide that worksheet and take you to Intro, the Login worksheet. Type in a name (based on Column A of Admin), then if that passes the first test, then it will ask for the appropriate password. This would be the corresponding password. If that works, then the Intro page is hidden, the ShowData worksheet will appear, and column B will have the appropriate data from the corresponding column in Admin (Columns H through L).

    Once you have that figured out, Then hit the END button on ShowData. Also, hide the Base and Admin worksheets. This leaves only the Intro worksheet available. Now try the login.

    =============

    Hope this is a start in the direction of what you had in mind.
    Attached Files Attached Files
    Last edited by shades; 10-15-04 at 17:43.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    I've created a simple password control and attached the example. It is not as complete as shades example but shows how it can be added for any workbook, with a userform for the password entry.

    You would keep the user passwords in the same workbook on a hidden sheet. though in the example it is not hidden for demo purposes.

    Of course this is not high security but to only give simple file access control example. Would need some work to add function for your use.

    Good Luck


    .
    Attached Files Attached Files
    ~

    Bill

  9. #9
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    Attached is a workbook that utilizes user names/passwords to allow access for individual users to only the sheets that you specify.

    It's pretty straightforward and the code is well documented.

    Hope it helps,

    Smitty

    (Heya Shades!)
    Attached Files Attached Files

  10. #10
    Join Date
    Oct 2003
    Posts
    1,091
    Hey, Smitty.

    I tried an experiment with both solutions. As you know, I work on both Office 2002 at work and Office 2004 (Mac) at home. I tried your solution, and like it's approach, but it doesn't work with XL2004 on the Mac. It gets hung up on the Dashboard line. On the other hand, my solution worked fine with XL2004 on the Mac.

    The reason I mention this is that sometimes people have to do cross-platofrm development. I know from other work with this, that UserForms are very particular in implementation for both platforms. It can be done, but requires, tweaking, testing, tweaking more.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  11. #11
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Good point Shades.

    VBA for the PC and MAC are entirely diferent animals, despite what Microsoft says.

    I don't have enough exposure to MACS to really be able to experiment enough.

    Smitty

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    Nor was it expected (based on the OP). I just threw that in, so that if people were trying to work cross-platform, they would be aware of this bump in the road.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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