PDA

View Full Version : Userform To Act As A Login With A Password To Use The Spreadsheet



dannyscroggi
04-11-2007, 05:17 PM
Hi,

I have come up with the idea to create a userform to act as a login page for my project.

I have the project complete and need a way to protect it.

I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF". Or If I have a table of 'users' and 'passwords' in sheet (USERS) to match them against that if it is easier?

Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins.

If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again.

Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password.

Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:

Load UserForm1
UserForm1.Show

I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet To show who is 'Logged On'.

If there is a way to display the password using asterix's for security that would be useful.

Sorry the post is long and I know it may take some time but it would be greatly appreciated if anyone can help.

Thanks in advance,

Danny

Bob Phillips
04-12-2007, 12:49 AM
I have come up with the idea to create a userform to act as a login page for my project.

I have the project complete and need a way to protect it.

I want the userform to display an input box asking the user to enter their 'login ID'. This has to be either "SUPERVISOR" or "STAFF". Or If I have a table of 'users' and 'passwords' in sheet (USERS) to match them against that if it is easier?
You could use a combox control and set the RowSource property of that control to point at the range of the sheet contatining the names. Better still, use a dynamic range name, then you can add to it without having to change the code. To do this, on the worksheet assuming the names are in column A, select A1, hit Ctrl-3, enter a name of NameList, enter a RefersTo value of
=OFFSET($A$1,,,COUNTA($A:$A)<1)
and OK out. Then enter NameList oin the RowSource property.

You can make ths heet hidden to stop people seeing the data


Upon correct entry i need it to then display another input box asking for a 'password'. This needs to be "SHOES" for both logins.

file:///C:/DOCUME%7E1/Bob/LOCALS%7E1/Temp/moz-screenshot.jpg If the login is not equal to SUPERVISOR or STAFF I need a message box displaying "username incorrect". and for it then to show the input box requesting the 'login ID' again.

Again if the password is not equal to "SHOES" to display a message box displaying "password incorrect" Then show the input box requesting the password.

Upon correct entry for both the username and password i would like my other userform to show. This is displayed by:

Load UserForm1
UserForm1.Show
This would be a simple textbox. Add a commandbutton to be pressed when both entries are complete with validation code like this



Private Sub cmdOK_Click()
If ComboBox1.Value <> "" And TextBox1.Text = "SHOES" Then
Me.Hide
UserForm1.Show
End If
End Sub

There is no need to check a valid name, because by using a combobox they cannot select an invalid name.



I would like the correct 'username' to be displayed in cell "D27" on the active sheet and 'password' to be displayed in cell "F27" also on the active sheet To show who is 'Logged On'.
Just extend the OK button code with



Private Sub cmdOK_Click()
If ComboBox1.Value <> "" And TextBox1.Text = "SHOES" Then
Me.Hide
ActiveSheet.Range("D27").Value = ComboBox1.Value
UserForm1.Show
End If
End Sub

note that I have showed the password, that is a bad idea, no point in having one if you print it!



If there is a way to display the password using asterix's for security that would be useful.
A textbox has a PasswordChar property. You can set this to any value, asterisk is the obvious one, and that character is echoed as the user types in a value.

dannyscroggi
04-12-2007, 03:32 AM
Thank you very much XLD that worked!

I have come up with the idea though that instead of having 2 users with the same password, to then have a table in a sheet named "Users" displaying 4 usernames each with their own password.

I would then need 2 text boxes to enter the 'username' and then the 'password'.

Then clicking the 'ok' button i would need it to lookup the username and password from my 'users' sheet and the 'users table'.

I would then like for the user to only be able to try 3 times to '"log in" after the 3 incorrect tries I would like the workbook to close. This may need a +1 counter for incorrect entry, also displaying the appropriate messagebox informing the user of incorrect entry of either username or password.

Any ideas anyone?

Thanks.

Bob Phillips
04-12-2007, 03:42 AM
I would keep the combobox for the name as we described above. You cab set up a similar dynamic range name for passwords, say PwdList, and check it lik so



If IsError(Application.Match(.Text, Worksheets("Users").Range("PwdList"), 0)) Then
MsgBox "Invalid password"
With Me.TextBox1
.SelStart = 0
.SelLength (Len(.Text))
.SetFocus
End With
End If

dannyscroggi
04-12-2007, 03:56 AM
xld I have tried the code you posted.

I currently have this for my 'ok' button

(sorry I dont know how to post code properly)


Private Sub CommandButton1_Click()

If ComboBox1.value <> "" And TextBox1.Text = "SHOES" Then
Me.Hide
ActiveSheet.Range("D27").value = ComboBox1.value
UserForm1.Show
End If

If IsError(Application.Match(ComboBox1.value, Worksheets("Users").Range("B2,B5"), 0)) Then
MsgBox "Invalid password"
With Me.TextBox1
.SelStart = 0
.SelLength (Len(.Text))
.SetFocus
End With
End If

End Sub



I get an error at '.SelLength'

Any fixes?
Edited 17-Apr-07 by geekgirlau. Reason: insert vba tags

Bob Phillips
04-12-2007, 04:37 AM
Sorry, my error. I don't think you need that first test if you are checking against a sheet



Private Sub CommandButton1_Click()

If IsError(Application.Match(ComboBox1.Value, Worksheets("Users").Range("B2,B5"), 0)) Then
MsgBox "Invalid password"
With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
End If

End Sub

geekgirlau
04-16-2007, 09:20 PM
Hi Danny,

Welcome to the Board :hi:

When you are posting code, the last step is to highlight the code text, then click on the "VBA" button. I've taken the liberty of editing your post to do this, so just keep it in mind for next time.