Private Sub cmdLogin_Click()
On Error GoTo ErrorHandler
Dim RowNo As Long
Dim Id As String
Dim pw As String
Dim ws As Worksheets
Application.ScreenUpdating = False
Set ws = Worksheets("User&Pass")
Id = LCase(Me.txtLogin)
RowNo = Application.WorksheetFunction.Match(Id, ws.range("A2:A999"), 0)
CleanExit:
Set ws = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
Exit Sub
ErrorHandler:
MsgBox "Unable to match ID, enter valid ID.", vbOKOnly
GoTo CleanExit
End Sub
我正在制作一个Excel用户表单,现在我需要添加一个登录界面来使其看起来更加专业。我已经开始使用上述代码,但是我遇到了一个死胡同。
我的目标是说,如果id和密码匹配,则加载工作簿或取消隐藏工作簿并继续。用户名和密码存储在名为"User&Pass"的工作表上。目标是从该工作表的A列读取用户名,B列读取密码,如果成功,我会隐藏该工作表,以便用户无法查看其他用户的信息。
使用我上面的代码,我只需要让它判断是否与用户列匹配,然后继续匹配相应密码列的内容,否则转到错误处理程序。
我可以进行格式设置,如隐藏和取消隐藏工作表等,只需要帮助读取用户名和密码。
非常感谢您的帮助!
Z
第一次修改尝试:
Private Sub cmdLogin_Click()
On Error GoTo ErrorHandler
Dim RowNo As Long
Dim Id As String
Dim pw As String
Dim ws As Worksheets
Application.ScreenUpdating = False
Set ws = Worksheets("User&Pass")
Id = LCase(Me.txtLogin)
RowNo = Application.WorksheetFunction.Match(Id, ws.range("A2:A999"), 0)
RowNo = RowNo + 1
pw = ws.range("B" & RowNo)
If pw = Me.txtLogin Then
'continue
txt1.Value = "yes"
Else
GoTo ErrorHandler
End If
CleanExit:
Set ws = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
Exit Sub
ErrorHandler:
MsgBox "Unable to match ID, enter valid ID.", vbOKOnly
GoTo CleanExit
End Sub
@siddarthRout
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
Application.ScreenUpdating = True
Set ws = Worksheets("Details")
Id = LCase(Me.txtLogin)
Set aCell = ws.Columns(1).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
'~~> Rest of your code. For example if the password is
'~~> Stored in Col B then
Debug.Print aCell.Offset(, 1)
Unload Me
FrmMenu.Show
'~~> You can then use the above aCell.Offset(, 1) to
'~~> match the password which the user entered
Else '<~~ If not found
MsgBox "Unable to match ID, enter valid ID.", vbOKOnly
End If
CleanExit:
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub