Setting password problem

[tiredofit]

Can somone please test this for me. It works fine on my PC but not on others.

In Sheet2, cells A1 and A2, put some text. Then run the ThisWorkbook code.

It seems to crash for my users when it tries to retrieve the password:

        .Unprotect Password:=MyPassword.Password

ThisWorkbook:

Option Explicit

    Dim PasswordArray() As Variant
    
Public Sub Workbook_Open()
    
    With Me
    
        Call .SetPassword
        
        Call .UsersList
        
    End With

    Set MyAuthorisedUser = New ClsVariables
    
    MyAuthorisedUser.AuthorisedUser = False
    
    Dim PasswordElementsCounter As Integer
    
    For PasswordElementsCounter = LBound(PasswordArray, 1) To UBound(PasswordArray, 1)
    
        If ModFunctions.GetUsername = PasswordArray(PasswordElementsCounter, 1) Then
        
            MyAuthorisedUser.AuthorisedUser = True
        
            Exit For
        
        End If
        
    Next PasswordElementsCounter
        
    With Sheet1
        
        .Unprotect Password:=MyPassword.Password
        
        
            
        .Protect Password:=MyPassword.Password
        
    End With
    
    Erase PasswordArray()
    
End Sub

Sub SetPassword()

    Set MyPassword = New ClsVariables
    
    MyPassword.Password = ModFunctions.GetUsername
    
End Sub

Sub UsersList()
    
    PasswordArray = Sheet2.Cells(1, 1).CurrentRegion.Value

End Sub

ModFunctions:

Option Explicit

    Global MyAuthorisedUser As ClsVariables
    
    Global MyPassword As ClsVariables

Function GetUsername() As String

    GetUsername = LCase(String:=Environ("UserName"))

End Function

ClsVariables:

Option Explicit
    
    Private pAuthorisedUser As Boolean
    Private pPassword As String
    
Public Property Get AuthorisedUser() As Boolean
   
 AuthorisedUser = pAuthorisedUser

End Property

Public Property Let AuthorisedUser(ByVal AUser As Boolean)

    pAuthorisedUser = AUser

End Property

Public Property Get Password() As String

    Password = pPassword

End Property

Public Property Let Password(ByVal PWord As String)

    pPassword = PWord

End Property

 

[RoryA]

Works fine for me. What do you mean by crash - crash Excel, error message, or something else?

 

[tiredofit]

The concept is as follows:</SPAN>

Instead of hard-coding a password, the program identifies the username logged on and sets that to be the password.</SPAN>

A sheet, Sheet2, holds the list of authorised users, so if the username is listed here, the sheet is unlocked, otherwise it remains locked.

When my users use it, it crashes, citing the password is not correct but that's impossible because the password is the username!

 

[RoryA]

But if you saved the workbook with the sheet protected, the password is your username, not theirs.

 

[tiredofit]

Thanks, I think that's the problem (though how comes it didn't crash when you tried it)?

 

[RoryA]

Because I wasn't using your workbook, so the sheets weren't protected with a different password to start with.

 

[tiredofit]

Of course, thanks.

Can you suggest a workaround? I like my concept of not having to hard-code a password.

 

[RoryA]

Unless there is one workbook per user, this won't work. If there is, then you merely need to unprotect the sheets before you send the workbook out.

 

[tiredofit]

Thanks, will do that.