Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Password Access (2002)

    Loungers - I need you assistance again. I have a spread sheet with a number of tabs. Some of the tabs contain confidential information that I want to restrict access to.
    I'm hoping that there is a way to display a Password screen when opening the spreadsheet that will hide or show selected tabs based on established password access levels - ie level 1 access - makes tab 1 & 2 visible: Level 2 access - makes tab 1 & 3 visible & administrator access makes all tabs visible. I hope that all makes sence!

    Any ideas?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Password Access (2002)

    There is no "built-in" way to do this. You would have to do the coding yourself (post back if you need assistance).

    I see the task as (note this is only "marginally secure")
    Very hide all but a main "title screen"
    (this would be displayed if macros are not enabled)
    If macros enabled, a password screen could be popped up asking for userid/password
    [you would have to keep a "list" somewhere for it to find (one of the worksheets perhaps). This list would have userID, password, "access type"]
    Based on userid entered, you would get the access type and "make visible" the appropriate worksheets.

    You would also have to (before saving/close) veryhide all the spreadsheets again so the access is redone.

    The VB project should be protected to prevent changes. You might also want workbook and worksheet protection added

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    There is nothing in Excel that would do this for you. You could write your own code and put it in the workbook open event routine to ask for a password and then unhide the sheets that you want that user to see. However, before you spend a lot of time doing this, I will warn you that there is no way to make this very secure. You can use Excel's security to protect access to this code, but breaking Excel's security is trivial. Anyone who wants to get into the code can get a password cracker from the WEB and be into the code in minutes. Once in the code, finding out how to look at the hidden sheet would be only a couple of more minutes.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    I think, not always the users are so malicious to look for as to break passwords
    if this is your situation, perhaps this example can help to you

    Before sending it to users already it must have very hidden all the sheets except one
    (the file is prepared for that)

    When you are going to open the file and it asks to you for the password you can type:
    pass1, pass2 or pass3

    pass1 = show the sheet2
    pass2 = show the sheet3
    pass3 = show the sheet2 and Sheet3 (all)


    The password of access to VBA is

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    Thanks you all for your feedback - The people that will be accessing the spreadsheet will be entering performance assessement information into an 'Employee' worksheet, their supervisor then enteres information in the the 'Superviror' worksheet and an administrator will review the combined results. The only reason that I want to hide/show respectives worksheets is to prevent the assessments being influanced by existing input data. I'm sure that the people doing this will not know how or want to crack the passwords. Thanks Servando for the code - anyone have any other suggestions?

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Password Access (2002)

    As noted, Excel's Protection and VBA password protection is a joke, though the typical user may not have the knowledge or inclination to defeat it. The only "secure" solution would be to send separate Employee and Supervisor worksheets, in their own separate workbooks, to the persons who will be completing them. When both sheets completed, combine into one workbook that goes to the Administrator. That's what I would do. You could use VBA to automate consolidation of the Employee/Supervisor worksheets by using some logical naming convention for files & worksheets.

    HTH

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    That is a good example. I was looking at the code so I can understand it however I do not understand how it works. I see where you set all the tabs except firstpage to veryhidden and I see where you show the form and check the password.

    Can you explain how the code works about accepting the password and calling the subroutine check?

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    Don:

    The routine is very simple... the only thing that does is to take the answer that obtains in the variable "pass"
    (Select Case pass)
    Then.. depending on the answer (Case "something") it executes the instructions (until the next case)

    Case "value(1) of pass"

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    Thank you Servando,

    I understand the routine - I just don't understand where the entry in Textbox1 of fPassW is assigned to the variable pass. Is this in Modulo 1? Also, I do not see in the code where Modulo 1 is called - how does Excel know when to run that sub? I have never worked with user forms in Excel so I thought the answer would be in the properties of the user form, the textbox, or the command button but I could not find it. Am I looking in the wrong place?

  10. #10
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Password Access (2002)

    Don...

    Double click on the button ACCEPT inside of VBA

Posting Permissions

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