Excel VBA & UserForm 登录名和密码 VLOOKUP 表格中的表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44346120/
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 & UserForm Login and Password VLOOKUP Table in Sheet
提问by akrasia
I've been trying to get my login userform to login when clicked based on data in a table in the workbook, but I just can't seem to get the code right.
我一直试图让我的登录用户表单在根据工作簿中表格中的数据单击时登录，但我似乎无法正确获取代码。
Details are:
详情如下：
Userform username textbox = UsernameTextbox;
Userform password textbox = PasswordTextbox;
Userform submit button = LoginButton
用户表单用户名文本框 = 用户名文本框；
用户表单密码文本框 = 密码文本框；
用户表单提交按钮 = LoginButton
My workbook has a number of sheets, one of which is "Users". In that sheet, there is a table called "Users_Table". That table has 4 columns:
我的工作簿有许多工作表，其中一张是"Users". 在该工作表中，有一个名为 的表"Users_Table"。该表有 4 列：
ID (individual IDs for users) [Column A],
Username [Column B],
Password [Column C],
Admin (answer is "True" or "False" depending on if they have admin rights) [Column D].
ID（用户的个人 ID）[A 列]，
用户名 [B 列]，
密码 [C 列]，
管理员（答案为“真”或“假”，取决于他们是否拥有管理员权限）[D 列]。
I'm trying to do this:
If the username and password is correct for a user AND if the admin column entry is False, then I want to show sheets "Quick Add"and "Overview", I want to make the sheet "Admin"hidden (not VeryHidden since I need to use data on this sheet for other macros), and make "User"sheets VeryHidden so those logged in can't see other users' details. But for users who correctly enter their username and password AND for whom the admin column entry is True, I want to show all sheets.
我正在尝试这样做：如果用户名和密码对用户来说是正确的，并且如果管理列条目为 False，那么我想显示工作表，"Quick Add"并且"Overview"我想让工作表"Admin"隐藏（不是非常隐藏，因为我需要使用此工作表上的其他宏的数据），并使工作"User"表非常隐藏，以便登录的人无法看到其他用户的详细信息。但是对于正确输入用户名和密码并且管理员列条目为 True 的用户，我想显示所有工作表。
This is what I have so far:
这是我到目前为止：
Private Sub LoginButton_Click()
Dim Username As String
Username = UsernameTextbox.Text
Dim password As String
Password = PasswordTextbox.Text
If IsNull(Me.UsernameTextbox) Or Me.UsernameTextbox = "" Then
MsgBox "You must enter your username.", vbOKOnly, "Required Data"
Me.UsernameTextbox.SetFocus
Exit Sub
End If
If IsNull(Me.PasswordTextbox) Or Me.PasswordTextbox = "" Then
MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Incomplete Entry"
Me.PasswordTextbox.SetFocus
Exit Sub
End If
Dim temp As String
On Error Resume Next
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, Worksheets("Users").Range("Users_Table"), 2, 0)
If Username = temp Then
Err.Clear
temp = ""
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, Worksheets("Users").Range("Users_Table"), 3, 0)
On Error Goto 0
If Password = temp Then
Sheets("Quick Add").Visible = xlSheetVisible
Sheets("Overview").Visible = xlSheetVisible
Sheets("Admin").Visible = xlSheetHidden 'This is now just Hidden and not VeryHidden since other macros need to use data on this sheet
Sheets("Users").Visible = xlVeryHidden
MsgBox "Password and Username Accepted. You are now Logged In."
'Unload Me
'Sheets("Quick Add").Select
'Range("A1").Select
Else
Sheets("Quick Add").Visible = xlVeryHidden
Sheets("Overview").Visible = xlVeryHidden
Sheets("Admin").Visible = xlVeryHidden
Sheets("Users").Visible = xlVeryHidden
MsgBox "Username and Password Combination Not Accepted"
End If
Else
Sheets("Quick Add").Visible = xlVeryHidden
Sheets("Overview").Visible = xlVeryHidden
Sheets("Admin").Visible = xlVeryHidden
Sheets("Users").Visible = xlVeryHidden
MsgBox "Invalid Username"
End If
End Sub
This works for the first entry in the "Users_Table", but it won't recognise the Username for the others (and so I don't know if it's recognising the Passwords for users as it's failing on the initial Username check). Any ideas what might be going wrong? I'm also not sure how I'd go about adding in the Admin requirement mentioned above. I need Admins ("True" in "Admin"column, i.e. Column D, in the "Users_Table") to be able to see all sheets; the code above is just for Users and shows "Quick Add"and "Overview"and hides "Admin"and "Users"sheets.
这适用于 中的第一个条目"Users_Table"，但它不会识别其他人的用户名（因此我不知道它是否正在识别用户的密码，因为它在初始用户名检查中失败）。任何想法可能会出错？我也不确定如何添加上述管理员要求。我需要管理员（"Admin"列中的“True” ，即列中的 D 列"Users_Table"）才能看到所有工作表；上面的代码仅仅是用户和节目"Quick Add"和"Overview"与生皮"Admin"和"Users"片材。
Any help would be much appreciated. Thank you!
任何帮助将非常感激。谢谢！
采纳答案by Siddharth Rout
You have made it very complicated. Keep it simple. Try this (untested)
你让它变得非常复杂。把事情简单化。试试这个（未经测试）
Private Sub LoginButton_Click()
Dim Username As String
Dim password As String
Dim passWs As Worksheet
Dim rng As Range
Dim CorrectDetails As Boolean
Username = UsernameTextbox.Text
password = PasswordTextbox.Text
If Len(Trim(Username)) = 0 Then
UsernameTextbox.SetFocus
MsgBox "Please enter the username", vbOKOnly, "Required Data"
Exit Sub
End If
If Len(Trim(password)) = 0 Then
PasswordTextbox.SetFocus
MsgBox "Please enter the password", vbOKOnly, "Incomplete Entry"
Exit Sub
End If
Set passWs = ThisWorkbook.Worksheets("Users")
With passWs
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To lrow
If UCase(Trim(.Range("B" & i).Value)) = UCase(Trim(Username)) Then '<~~ Username Check
If .Range("C" & i).Value = password Then '<~~ Password Check
CorrectDetails = True
'~~> Admin is True
If .Range("D" & i).Value = "True" Then
'
'~~> Do what you want
'
Else
'
'~~> Do what you want
'
End If
Exit For
End If
End If
Next i
'~~> Incorrect Username/Password
If CorrectDetails = False Then
MsgBox "Invalid Username/Password"
End If
End With
End Sub
My Assumptions
我的假设
In sheet "Users", Col B has username, Col C has password and Col D has Admin values.. If not then please amend the above code as required.
在“Users”表中，Col B 有用户名，Col C 有密码，Col D 有 Admin 值。如果没有，请根据需要修改上面的代码。
回答by A.S.H
Any ideas what might be going wrong?
任何想法可能会出错？
There are a few errors in the code that don't match your description.
代码中有一些错误与您的描述不符。
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, _ Worksheets("Users").Range("Users_Table"), 2, 0) If Username = temp Then
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, _ Worksheets("Users").Range("Users_Table"), 2, 0) If Username = temp Then
Here you are matching the UsernameTextboxto column A (ID). The test for existence of the username should be in column B not A. The same mistake is made where you are matching the username onto the IDcolumn A insread of the column B of user names:
在这里，您将 匹配UsernameTextbox到 A 列 ( ID)。用户名是否存在的测试应该在 B 列而不是 A 列中。如果您将ID用户名与用户名 B 列的 A 列相匹配，则会犯同样的错误：
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, _ Worksheets("Users").Range("Users_Table"), 3, 0)
temp = WorksheetFunction.VLookup(Me.UsernameTextbox.Value, _ Worksheets("Users").Range("Users_Table"), 3, 0)
The best approach would be to fetch to row of the user at once (if it exists) and from there get all the attributes.
最好的方法是立即获取用户的行（如果存在）并从那里获取所有属性。
Private Sub LoginButton_Click()
' Get the user row or exit if not found
Dim r As Range
Set r = Worksheets("Users").Range("Users_Table").Columns(2) _
.Find(UsernameTextbox.text, , xlValues, xlWhole)
If r Is Nothing Then
MsgBox "username not found."
Me.UsernameTextbox.SetFocus
Exit Sub
End If
If Me.PasswordTextbox.Value <> r.Offset(, 1).Value2 Then
MsgBox "Wrong Password."
Me.PasswordTextbox.SetFocus
Exit Sub
End If
' So far user and password are ok
Dim isAdmin As Boolean: isAdmin = r.Offset(, 2).Value2
Sheets("Quick Add").Visible = xlSheetVisible
Sheets("Overview").Visible = xlSheetVisible
Sheets("Admin").Visible = IIf(isAdmin, xlSheetVisible, xlSheetHidden)
Sheets("Users").Visible = IIf(isAdmin, xlSheetVisible, xlSheetVeryHidden)
End Sub
