Excel vba 用户名/密码查找
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9720919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel vba username/ password lookup
提问by Zenaphor
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
I've got an excel userform i've been working on and now I need it to look more professional by having a log-in screen. I've started with the code above but I have come to a dead end.
我有一个我一直在研究的 excel 用户表单，现在我需要它通过登录屏幕看起来更专业。我已经从上面的代码开始，但我已经走到了死胡同。
how its set up my aim is to say if id & password matches then load up workbook or unhide the workbook and continue. the username and password are on a sheet called "User&Pass" Aim is it reads from there in columns a- user / b- pw respectively and if it's a success I will hide that sheet so they cant see other user's information
如何设置我的目标是说如果 id 和密码匹配，则加载工作簿或取消隐藏工作簿并继续。用户名和密码位于名为“User&Pass”的工作表上，目的是分别从 a-user/b-pw 列中读取，如果成功，我将隐藏该工作表，以便他们看不到其他用户的信息
with what I started above I just need it to say if it matches usercolumn then corresponding pw next door to it continue else go to my errorhandler
使用我上面开始的内容，我只需要说它是否与 usercolumn 匹配，然后在它旁边的相应密码继续，否则转到我的错误处理程序
i can do the formatting about hiding and unhiding sheets etc just need help with reading username and pw
我可以对隐藏和取消隐藏工作表等进行格式化，只需要阅读用户名和密码的帮助
thanks very much in advance Z
非常感谢提前Z
Editted attempt one;
编辑尝试一；
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
@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
采纳答案by Siddharth Rout
TESTED AND TRIED
测试和尝试
Is this what you are trying?
这是你正在尝试的吗？
CODE
代码
Option Explicit
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
If Len(Trim(txtLogin)) = 0 Then
txtLogin.SetFocus
MsgBox "Username cannot be empty"
Exit Sub
End If
If Len(Trim(txtPassword)) = 0 Then
txtPassword.SetFocus
MsgBox "Password cannot be empty"
Exit Sub
End If
Application.ScreenUpdating = False
Set ws = Worksheets("User&Pass")
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
If Me.txtPassword = aCell.Offset(, 1) Then
FrmMenu.Show
Unload Me
Else
MsgBox "Unable to match UserID or PasswordID, Please try again", vbOKOnly
End If
Else '<~~ If not found
MsgBox "Unable to match UserID or PasswordID, Please try again", vbOKOnly
End If
CleanExit:
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub
TIP:
提示：
Never let your user know (from security perspective) what was incorrect - The username or the password. Always show a generic message like "Unable to match UserID or PasswordID, Please try again":)
永远不要让您的用户知道（从安全角度）什么是不正确的 - 用户名或密码。始终显示一条通用消息，例如“无法匹配用户 ID 或密码 ID，请重试”:)
HTH
HTH
Sid
锡德
回答by SWa
Another way
其它的办法
On Error Resume Next
If Me.password <> Application.VLookup(Me.username, Sheet1.Cells(1, 1).CurrentRegion, 2, False) Then
MsgBox ("incorrect")
Exit Sub
Else
MsgBox ("Correct Password Entered")
End If
Also you will need to make sure that all your sheets are xlSheetVeryHidden from the outset to combat having macros disabled and un-hide them as part of your successful log in routine. You'll also want to set a password on your VBA project to prevent people unhiding the sheets. Bear in mind however, Excel is about as secure as a wet paper bag ;)
此外，您还需要确保从一开始就将所有工作表都设置为 xlSheetVeryHidden，以防止禁用宏并在成功登录例程中取消隐藏它们。您还需要在 VBA 项目上设置密码以防止人们取消隐藏工作表。但是请记住，Excel 与湿纸袋一样安全；)
