Setting password problem


Well-known Member
Apr 11, 2013
Office Version
  1. 365
  2. 2019
  1. Windows
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


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


Option Explicit

    Global MyAuthorisedUser As ClsVariables
    Global MyPassword As ClsVariables

Function GetUsername() As String

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

End Function


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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Works fine for me. What do you mean by crash - crash Excel, error message, or something else?
Upvote 0
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!
Upvote 0
But if you saved the workbook with the sheet protected, the password is your username, not theirs.
Upvote 0
Thanks, I think that's the problem (though how comes it didn't crash when you tried it)?
Upvote 0
Because I wasn't using your workbook, so the sheets weren't protected with a different password to start with.
Upvote 0
Of course, thanks.

Can you suggest a workaround? I like my concept of not having to hard-code a password.
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back