Excel VBA和工作表中的用户窗体登录名和密码VLOOKUP表 [英] Excel VBA & UserForm Login and Password VLOOKUP Table in Sheet

问题描述

我一直在尝试根据工作簿中表中的数据单击登录用户名，以便登录，但是我似乎无法正确获取代码.

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.

详细信息是:

用户名用户名文本框= UsernameTextbox;

Userform username textbox = UsernameTextbox;

用户表单密码文本框= PasswordTextbox;

Userform password textbox = PasswordTextbox;

用户提交按钮= LoginButton

Userform submit button = LoginButton

我的工作簿有许多工作表，其中之一是"Users" .在该工作表中，有一个名为"Users_Table" 的表.该表有4列:

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:

ID(用户的个人ID)[A列]，

ID (individual IDs for users) [Column A],

用户名[B列]，

密码[C列]，

管理员(答案是"True"还是"False"，取决于他们是否具有管理员权限)[D列].

Admin (answer is "True" or "False" depending on if they have admin rights) [Column D].

我正在尝试这样做:如果用户名和密码对于用户是正确的，并且如果admin列条目为False，那么我想显示工作表快速添加" 和概述" ，我想隐藏工作表"Admin" (不是VeryHidden，因为我需要将此工作表上的数据用于其他宏)，并制作"User" 工作表VeryHidden因此登录的用户看不到其他用户的详细信息.但是对于正确输入用户名和密码并且admin列条目为True的用户，我想显示所有工作表.

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.

这是我到目前为止所拥有的:

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

这适用于"Users_Table" 中的第一个条目，但不会识别其他用户的用户名(因此，我不知道它是否将用户的密码识别为初始用户名检查失败).任何想法可能出什么问题吗?我也不确定如何添加上述的Admin要求.我需要管理员("Admin" 列中的"True"，即"Users_Table" 中的D列)才能查看所有工作表；上面的代码仅适用于用户，并显示快速添加" 和概述" ，并隐藏"Admin" 和用户" 工作表.

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.

任何帮助将不胜感激.谢谢！

Any help would be much appreciated. Thank you!

推荐答案

您已经使其变得非常复杂.把事情简单化.试试这个(未经测试)

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

我的假设

在用户"表中，Col B具有用户名，Col C具有密码，Col D具有管理员值.如果没有，请根据需要修改以上代码.

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.

这篇关于Excel VBA和工作表中的用户窗体登录名和密码VLOOKUP表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！