excel - 根据用户表单中的文本框值输入单元格值

Question

我有一个用户表单“LoginForm”和一个电子表格“AMchoices”。

当输入他们的登录详细信息时，用户将他们的用户 ID 输入到 LoginForm 上的“txtUser”文本框中。接受输入后，AMForm 将打开。如果他们的凭据被接受，我希望他们的用户 ID 出现在“AMchoices”的单元格 B3 中。由于有多个用户登录，我希望将其输入到下一个空行。

我该如何编码？请告诉我。

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

Answer 1

下面的代码应该做你想做的事。

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

将它集成到现有代码中的最佳位置应该是在“成功登录”消息之前或之后，除了在顶部更好的变量声明。实际上，此时不再需要变量 RowNo，您可以重新使用它来替换我代码中的 R。