Username and password login.

[Moh]

i currently have a userform1 with textbox1, textbox2, CommandButton1 and
CommandButton2.

Textbox1 = Username
TextBox2 = Password
CommandButton1 = Login
CommandButton2 = Quit

All usernames are stored in Worksheet "User" in column A
All passwords are stored in user Worksheet "User" in column B
E.g
column A COLUMN B
USERNAME PASSWORD
Frank 4582
Tony 8514
Richard 9587
Louise 5214
Jen 7412

What i want the userform to do for me is:

user selects Login (Commandbutton1) If the username matches the password
then open Worksheet "Sheet1"
e.g A2 USER1 B2 PASSWORD2

If the username or password is incorrect it shouls bring up a message box
"Invalid Username/Password - please check and try again" also clear out
textbox1 and textbox2.

If the user selects Quit (Commandbutton2) then it should exit excel without
asking to save the document.

Also any way we can grey out the Close button (X) on the Userform1?


Merry Christmas
Pls Help

 

[Jean-Yves]

Hi,

something like
Private Sub CommandButton1_Click()
Dim rng As Range
Dim strUser As String
Dim strPW As String

Set rng = ThisWorkbook.Worksheets("User").Range("A:B")
strUser = Me.TextBox1
strPW = Me.TextBox2
On Error Resume Next
strPW = Application.WorksheetFunction.VLookup(strUser, rng, 2, False)
On erro GoTo 0
If Me.TextBox2 = strPW Then
Worksheets("Sheet1").Activate
Unload Me
Else: Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox1.SetFocus
MsgBox "Invalid Username/Password - please check and try again",
vbExclamation, "Login"
End If
End Sub

Private Sub CommandButton2_Click()
ThisWorkbook.Saved = True
Application.Quit
End Sub

Please not that is is not really good to keep the creation name of your
controls (eg CommandButton1)
Instead, rename them before you create code (eg cmdLogin, cmdCancel,
txtUser, txtPassword)
Regards
JY

 

[Jean-Yves]

forgot to finsih my own correctin
Private Sub CommandButton1_Click()
Dim rng As Range
Dim strUser As String
Dim strPW As String

Set rng = ThisWorkbook.Worksheets("User").Range("A:B")
strUser = Me.TextBox1
On Error Resume Next
strPW = Application.WorksheetFunction.VLookup(strUser, rng, 2, False)
On Error GoTo 0
If Me.TextBox2 = strPW Then
Worksheets("Sheet1").Activate
Unload Me
Else: Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.TextBox1.SetFocus
MsgBox "Invalid Username/Password - please check and try again",
vbExclamation, "Login"
End If
End Sub
Private Sub CommandButton2_Click()
ThisWorkbook.Saved = True
Application.Quit
End Sub
Regards
JY

 

[Moh]

problem its not working..

it says:
compile error in hidden module: Userform1

 

[Jean-Yves]

On the VBA menu, "debug", select "compile,
Where does it stop or show an error
JY

 

[Moh]

i run the compiler and it has the following highlighted:
vbExclamation

 

[Jean-Yves]

This should all be on on line :
MsgBox "Invalid Username/Password - please check and try again",JY

 

[Moh]

hi Jean-Yves

i got it working ... thank you

How do i disable the X Button on the userform1?

cheers

 

[Jean-Yves]

Moh,

You already ask that question in a previous postand it was answers.

Regards
JY

 

[Jean-Yves]

Don't forget to debug.
Test
if nothing (or space) is typed,
upperr or lower case
Regards
JY

 

[theSquirrel]

hi Jean-Yves

i got it working ... thank you

How do i disable the X Button on the userform1?

cheers

I use code like this to just run the close button code

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
'Runs the Cancel button
If CloseMode = vbFormControlMenu Then
CommandButton2 _Click
End If
End Sub

theSquirrel