PDA

View Full Version : trouble selecting sheet



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

SamT
03-27-2014, 04:51 PM
'~~> If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPassword = aCell.Offset(, 1) Then
GoTo CleanExit '<------------------------------------------------------------------
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
Sheets("Home").Select '<----------------------------------------------------------

ErrorHandler:
MsgBox Err.Description
Resume CleanExit

End Sub