CODEWITHSUNDEEP
excelvba
MJ98
MJ98

Reputation: 87

Entering cell value based on text box value in userform

I have one Userform "LoginForm" and one spreadsheet "AMChoices".

When entering their login details, the user enters their UserID into "txtUser" text box on the LoginForm. When entry is accepted, AMForm opens. I want their UserID to appear in cell B3 in "AMChoices" if their credentials are accepted. As there are multiple users logging in, I want it to be entered to the next empty row.

How can I code this? Please let me know.

Private Sub btnLogin_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(txtUser)) = 0 Then
    txtUser.SetFocus
    MsgBox "Error. UserID cannot be empty."
    Exit Sub
End If

If Len(Trim(txtPass)) = 0 Then
    txtPass.SetFocus
    MsgBox "Error. Password cannot be empty."
    Exit Sub
End If

Application.ScreenUpdating = False

Set WS = Worksheets("StudentInformation")
ID = LCase(Me.txtUser)

Set aCell = WS.Columns(1).Find(What:=ID, LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)

If Not aCell Is Nothing Then
    RowNo = aCell.Row
    If Me.txtPass = aCell.Offset(, 1) Then
        MsgBox "Login Successful."
        Unload Me
    Else
        MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
    End If

Else
    MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If

'Opening specific Userform
If aCell.Offset(, 4) = "SBUB10" Then AMForm.Show
If aCell.Offset(, 4) = "SBUB20" Then FMForm.Show
If aCell.Offset(, 4) = "SBUB30" Then HRMForm.Show



CleanExit:
Set WS = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox err.Description
Resume CleanExit
End Sub

Upvotes: 1

Views: 81

Answers (2)

Santosh
Santosh

Reputation: 12353

Add below code after AMForm opens

With Worksheets("AMChoices")
      lastrow = .Range("B" & .Rows.CountLarge).End(xlUp).Row + 1
      If lastrow < 3 Then lastrow = 3
      .Cells(lastrow, "b") = WorksheetFunction.Proper(ID)
End With

Upvotes: 1

Variatus
Variatus

Reputation: 14373

The code below should do what you want.

Dim R As Long

With Worksheets("AMChoices").Columns("B")
    R = 3
    Do While Len(.Cells(R).Value)
        R = R + 1
    Loop
    .Cells(R).Value = ID
End With

The best place to integrate it into your existing code should be just before or just after the "successful login" message, except for the variable declaration which is better off at the top. In fact, the variable RowNo is no longer needed at that point and you might re-purpose it to replace the R in my code.

Upvotes: 1

Related Questions