ashgull80
03-27-2014, 02:38 PM
hi im using this code to log into as workbook but im having trouble with selecting a worksheet once the userform is selected.
when the user form closes i would like to select the home sheet and insert the users name in cell 2,3 and the time in cell 2,4
could someone let me know where im going wrong please.
thank you
Private Sub cmdLogin_Click()
Dim RowNo As Long
Dim Id As String, pw As String
Dim ws As Worksheet
Dim aCell As Range
On Error GoTo ErrorHandler
If Len(Trim(cboUserName)) = 0 Then
cboUserName.SetFocus
MsgBox "Username cannot be empty", _
vbOKOnly, "Username Error"
Exit Sub
End If
If Len(Trim(txtPassword)) = 0 Then
txtPassword.SetFocus
MsgBox "Password cannot be empty", _
vbOKOnly, "Password error"
Exit Sub
End If
Application.ScreenUpdating = False
Set ws = Worksheets("Users")
Id = LCase(Me.cboUserName)
Set aCell = ws.Columns(2).Find(What:=Id, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'~~> If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPassword = aCell.Offset(, 1) Then
Unload Me
Else
MsgBox "Unable to match User or Password, Please try again", _
vbOKOnly, "Password Error"
End If
Else '<~~ If not found
MsgBox "Unable to match User or Password, Please try again", _
vbOKOnly, "Password Error"
End If
CleanExit:
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
Sheets("Home").Select
End Sub
when the user form closes i would like to select the home sheet and insert the users name in cell 2,3 and the time in cell 2,4
could someone let me know where im going wrong please.
thank you
Private Sub cmdLogin_Click()
Dim RowNo As Long
Dim Id As String, pw As String
Dim ws As Worksheet
Dim aCell As Range
On Error GoTo ErrorHandler
If Len(Trim(cboUserName)) = 0 Then
cboUserName.SetFocus
MsgBox "Username cannot be empty", _
vbOKOnly, "Username Error"
Exit Sub
End If
If Len(Trim(txtPassword)) = 0 Then
txtPassword.SetFocus
MsgBox "Password cannot be empty", _
vbOKOnly, "Password error"
Exit Sub
End If
Application.ScreenUpdating = False
Set ws = Worksheets("Users")
Id = LCase(Me.cboUserName)
Set aCell = ws.Columns(2).Find(What:=Id, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'~~> If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPassword = aCell.Offset(, 1) Then
Unload Me
Else
MsgBox "Unable to match User or Password, Please try again", _
vbOKOnly, "Password Error"
End If
Else '<~~ If not found
MsgBox "Unable to match User or Password, Please try again", _
vbOKOnly, "Password Error"
End If
CleanExit:
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
Sheets("Home").Select
End Sub