CODEWITHSUNDEEP
excelvba
MJ98
MJ98

Reputation: 87

Entering Cell Value based on Userform entry

I have 1 userform for login called "LoginForm" and 3 additional userforms "AMForm", "FMForm" and "HRMForm" that open up if the user's details are correct. There are 3 spreadsheets "AMChoices", "FMChoices" and "HRMChoices" where the contents from the 3 additional userforms are recorded into the relevant spreadsheet i.e. FMForm into FMChoices.

I want their UserID to start to appear in cell B3 in the relevant spreadsheet if their credentials are accepted. For example, if userform "AMForm" their UserID is entered into the next available cell in column B in "AMChoices". As there are multiple users logging in, I want it to be entered to the next empty row.

I have entered the current code for the login which works. However, it still enters the UserID into all sheets rather than the specific one. How can I isolate it to enter only in the correct one?

The relevant part of the code starts at 'Opening Specific Userform

Many thanks in advance :)

Private Sub btnLogin_Click()
    Dim RowNo As Long
    Dim ID As String, PW As String
    Dim WS As Worksheet
    Dim aCell As Range
    Dim LastRow As Long

    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 = (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
    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

    If aCell.Offset(, 4) = "SBUB20" Then FMForm.Show
    With Worksheets("FMChoices")
      LastRow = .Range("B" & .Rows.CountLarge).End(xlUp).Row + 1
      If LastRow < 3 Then LastRow = 3
      .Cells(LastRow, "b") = WorksheetFunction.Proper(ID)
    End With

      If aCell.Offset(, 4) = "SBUB30" Then HRMForm.Show
      With Worksheets("HRMChoices")
      LastRow = .Range("B" & .Rows.CountLarge).End(xlUp).Row + 1
      If LastRow < 3 Then LastRow = 3
      .Cells(LastRow, "b") = WorksheetFunction.Proper(ID)
    End With

    If aCell.Offset(, 6) = "Admin" Then MsgBox "Administrator recognised." & Chr(13) & "You can now access students' choices on the spreadsheets below."


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

Upvotes: 0

Views: 45

Answers (1)

SJR
SJR

Reputation: 23081

Think you just need to restructure your If statements. If you place the Then on the same line then all follwing lines get acted upon irrespective of whether the condition is met (i.e. they are not part of the If clause).

See this.

If aCell.Offset(, 4) = "SBUB10" Then
    AMForm.Show
    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
ElseIf aCell.Offset(, 4) = "SBUB20" Then
    FMForm.Show
    With Worksheets("FMChoices")
        LastRow = .Range("B" & .Rows.CountLarge).End(xlUp).Row + 1
        If LastRow < 3 Then LastRow = 3
        .Cells(LastRow, "b") = WorksheetFunction.Proper(ID)
    End With
ElseIf aCell.Offset(, 4) = "SBUB30" Then
    HRMForm.Show
    With Worksheets("HRMChoices")
        LastRow = .Range("B" & .Rows.CountLarge).End(xlUp).Row + 1
        If LastRow < 3 Then LastRow = 3
        .Cells(LastRow, "b") = WorksheetFunction.Proper(ID)
    End With
End If

Upvotes: 1

Related Questions