excel Password algorithm

[CyberJar]

Hello,

For an Excel VBA app, is this the accepted way to check a password? (No Access involved - just a straight Excel app)

In a hidden cell on a hidden Worksheet, I put the password. Using the Worksheet Function MATCH, I check what the password is against what the User entered into the Textbox.
If correct, give them access to whatever, If incorrect, notify and let them try again.

Is this the way it is usually done?

Thanks for any advice,
CJ

[RobDog888]

If the user deletes the worksheet after unhiding it there will be
no password verification at all. It may be better to add a class to
your Excel VBA that will hold the password. and in the
Open_Workbook event, or similar, you can prompt for the
password and validate it. You can protect the code from being
viewed by clicking (in VBA) the Tools > VBAProject Properties... >
Protection tab > and check the "Lock project for viewing" box and
supply a password. Don't forget it because there is now way to
reset it or retrieve it once you forget it.

HTH

[CyberJar]

Your way is very professional of course.

If I put the password on the Data worksheet, it will not get deleted (if that page is deleted, then there is no app)

On second thought as I'm typing , the cell containing the password may get erased accidentally by some Administrator.

What if I hardcode the password value, then lock the project for viewing only.

CJ

[RobDog888]

What if I hardcode the password value, then lock the project for viewing only.
The answer is yes.
Much better.

Just make sure to write the password down and before you lock
the project make a backup copy without locking, just in case.

[TheFIDDLER]

If you are coding your password into VBA, try and keep it within a subroutine, as a local variable. For best practices, only the code subroutine that requires access to it should be able to see it.

And then it goes without saying of locking your project.

I have also seen using names to store variables. You can also hide the name from being visible. Not as secure, but few people tend to look for hidden names. Use this if you require access to your password from many different components of your project.

[TheFIDDLER]

you also asked what was the accepted way to check for a password.

this is likely what I would use, coding in a vba module.


Sub password()
Dim password As String
Dim i As Integer
Dim x As String
password = "Unlock" ' case sensitive
For i = 1 To 3
x = InputBox("Enter correct password", vbYesNo)
If x = password Then
MsgBox "You have just won absolutely nothing! Congratulations!", vbOKOnly, "System Password"
Exit For
Else
MsgBox "You lost", vbOKOnly, "System Password"
End If
Next
End Sub


If you choose to still go with a cell stored variable, you can still use this with password just referencing the cell.
The i is used to give the user three tries before it exits the loop.

[CyberJar]

Thanks for the replies.

Fiddler, I see that in your code you use an InputBox to capture the User's password. So you don't use the '*' input mask?

I had considered using an InputBox, but instead made a UserForm with a TextBox, Label, an Ok and a Cancel button. For all that overhead, I would have preferred a simple InputBox but because of the lack of an input mask I chose the UserForm.

I think the asterisks make the Users *feel* more secure, although it's a false sense of security.

CyberJar

[TheFIDDLER]

I guess it depends on your users.

Masked text boxes ("*****") are nice for session logon, gaining access to certain parts of the program, etcs.. I use these within my program at many points, say to restrict access to a price grid, or to view cost information.

Unmasked password boxes are often used when the password is more complex, involving many characters, and upper and lower case combinations. I would use if coding an install password that responds to the users' serial number. I would likely not use for a day to day system use password.
In my install routine, it involves a 16 to 20 digit code that the user has to type in. I always try to get the user to read it back to me so that I don't have to go through the routine twice. Hence, visible characters reduce the errors.

[CyberJar]

Good points on when to use visible passwords.

For my app, most Users are not going to use the Password feature - only Admin types when data changes - which is not too often.

Thanks to both you & Robdog888 for the assist,
CyberJar

[dis1411]

never hard code a password... you can open the file in notepad and see it