Results 1 to 7 of 7
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Multiple Passwords (XP)

    Can I setup something in an Excel spreadsheet so that multiple people have password access to it and each have their own password? Or as an alternative, let anyone see the menu but only those designees can run the macros.

    Rather than give blanket access to anyone that has THE password; I want to require each one to enter their UserID and password combination.
    Alan

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Multiple Passwords (XP)

    Alan,

    i've never had to use multiple password access to a workbook, but the following ought to work.
    Stick it at the front of your macro...
    '___________________________

    Private LoggedIn As Boolean' Module level variable

    Sub PasswordAccessToFile()

    If Not LoggedIn Then
    Dim Uid As String
    Dim Upw As String
    Dim N As String

    Uid = InputBox(vbCr & "Enter your User Identification", "Open Restricted File", "123456")
    Upw = InputBox(vbCr & "Enter your password", "Open Restricted File", "My Secret")

    Select Case Uid
    Case "09"
    If Upw = "Larry" Then N = "OK"
    Case "19"
    If Upw = "Moe" Then N = "OK"
    Case "29"
    If Upw = "Curly" Then N = "OK"
    Case "39"
    If Upw = "Tarzen" Then N = "OK"
    Case "49"
    If Upw = "Jane" Then N = "OK"
    Case Else
    MsgBox "You are out of here", vbCritical, "A violation message will be logged"
    Exit Sub
    End Select

    If N = "OK" Then
    MsgBox "You did good ", vbInformation, "You May Continue"
    LoggedIn = True
    'Run your code
    Else
    MsgBox "You are out of here", vbCritical, "A violation message will be logged"
    exit sub
    End If
    End If

    End Sub

    Regards,
    Jim Cone
    San Francisco, CA

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Multiple Passwords (XP)

    Thanks.... that is great! I made a minor change. I hide the information like UserIDs and passwords and use named cells to get the info. That way, when there are new users, the fields can be changed and not the macro:

    <pre>Private LoggedIn As Boolean ' Module level variable

    Sub PasswordAccessToFile()
    LoggedIn = False
    If Not LoggedIn Then
    Dim Uid As String
    Dim Upw As String
    Dim N As String

    Uid = InputBox(vbCr & "Enter your User Identification", _
    "Open Restricted File", "UserID")
    Upw = InputBox(vbCr & "Enter your password", _
    "Open Restricted File", "*****")

    Select Case Uid
    Case [Tom1], [Tom2], [Tom3] 'User names are hidden on the worksheet
    If Upw = [TomPW] Then N = "OK"
    Case [April1], [April2], [April3]
    If Upw = [AprilPW] Then N = "OK"
    Case [Roger1], [Roger2], [Roger3]
    If Upw = [Rogerpw] Then N = "OK"
    Case [Alan1], [Alan2], [Alan3]
    If Upw = [AlanPW] Then N = "OK"
    Case [NewYork1], [NewYork2], [NewYork3]
    If Upw = [NYpw] Then N = "OK"
    Case Else
    MsgBox "You are out of here", _
    vbCritical, "A violation message will be logged"
    Exit Sub
    End Select

    If N = "OK" Then
    MsgBox "You did good ", vbInformation, "You May Continue"
    LoggedIn = True
    'Run your code

    'End Code
    Else
    MsgBox "You are out of here", vbCritical, "A violation message will be logged"
    Exit Sub
    End If
    End If
    End Sub

    </pre>

    Alan

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Multiple Passwords (XP)

    Alan,
    Glad it helped. A couple of observations...

    It appears that my Case "Else" could be removed without really affecting the operation of the code.
    --
    The "LoggedIn" module level variable purpose is to require a password only upon first access to the code.
    Only after closing and reopening the workbook is a password needed again.
    Setting "LoggedIn" to false disables it. If you want a password entered on every use of the code,
    then remove the variable and the "If Not LoggedIn ..." line of code.
    --

    Regards,
    Jim Cone

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Passwords (XP)

    Beware, that if your users load my Name Manager from the Excel MVP page below, they can directly see your "hidden" information.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Multiple Passwords (XP)

    <font face="Comic Sans MS">The users I am working would not think to research on-line. If they are that smart, they can have my job.</font face=comic> <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    Alan

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Passwords (XP)

    I see you already have a suitable answer for your problem. I too have done this before but used a slightly different approach. Knowing that Excel isn't highly secure you have to work with what you've got but I had a project where I needed to offer different features based on who the used the tool. I had a generic 'guest' user, a list of specific user IDs, an Admin and a SuperAdmin. I used a veryhidden/protected worksheet to hold a look up table that contain this info as well as the password. At login I compared their username against that table. Some access was restricted to my company's employees only so I also did some registry checks since we all have a custom Windows image on our PCs vs. non-employees running the program. I also added a time bomb which disallowed access after 3 months since their first log-in. Phewww... a lot of work!

    As with NameManager spying on hidden Names, this can be broken too but we have no choice if we want to keep using Excel as the tool. Fortunately (in my case) very few people who use these tools know much at all about VBA (or that it exists) so in this case ignorance is bliss. For stuff that I create to distribute to non-employees we have a legal disclosure they have to sign (which doesn't guarantee that they won't give it to the competition or hack it themselves either). This legal disclosure is also the initial worksheet they see and they have to click the Accept button to proceed (else the workbook closes and never shows them the other worksheets).

    Enjoy!

    Deb

Posting Permissions

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