时间戳

首页 > 解决方案 > 根据用户表单中的文本框值输入单元格值

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

问题描述

我有一个用户表单“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

标签： excelvba

解决方案

AMForm 打开后添加以下代码

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

推荐阅读