excel VBA for VBA(登录系统)

Question

嗨，我只是好奇为什么在试图运行代码时会出现这个错误。下面的代码是一个登录系统。发生的错误是“没有下一步”，我已经多次尝试重构代码，但它仍然不起作用。我在VBA方面没有太多经验，所以任何帮助都是非常感谢的。

Sub CommandButton1_Click()
Dim Username As String
Username = Range("D6").Value
UsernameLength = Len(Username)
MsgBox (UsernameLength)

Dim PasswordLength As String
Password = Range("D10").Value
PasswordLength = Len(Password)
MsgBox ("Password length is:" + PasswordLength)

If PasswordLength = 0 And UsernameLength = 0 Then
  MsgBox ("Please enter login details or create an account")
Else
Set rng = Range("A1:A1000")
For Each row In rng.Rows
For Each cell In row.Cells
            Username_Lines = Username_Lines + 1
            Password_Lines = Password_Lines + 1
            If cell = Username Then
                Correct_Username = True
                a = Username_Lines
                Lines = a - b
                MsgBox ("True")
                Stop
            Else
               Correct_Password = False
               Correct_Username = False
               MsgBox ("False")
               End If
               Next
If Lines = 0 Then
    Correct_Password = True
    Correct_Username = True
Else:
    Correct_Password = False
    Correct_Username = False
    End If
               
If Correct_Password And Correct_Username = True Then
        Worksheets("MainSystem").Visible = xlSheetVisible
        Worksheets("LoginSystem").Visible = Not xlSheetVisible

ElseIf Correct_Password = Sql Or Correct_Username = Sql Then
        MsgBox ("Login Failed")

ElseIf Correct_Password And Correct_Username = False Then
        MsgBox ("Login Failed")
        
Else:
    MsgBox ("Login Failed")
          
End If
End Sub

Answer 1

考虑使用Match替代For每一个循环。

Option Explicit

Private Sub CommandButton1_Click()
    Dim username As String, password As String
    Dim ws As Worksheet, lastrow As Long, rng As Range, r
    
    Set ws = ActiveSheet '  better as Sheets("Sheetname")
    With ws
        username = .Range("D6").Value
        password = .Range("D10").Value
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A1:A" & lastrow)
    End With
    
    If Len(username) = 0 Or Len(password) = 0 Then
        MsgBox "Please enter login details or create an account", vbCritical
        Exit Sub
    End If
    
    ' find username
    r = Application.Match(username, rng, 0)
    If IsError(r) Then
         MsgBox ("Login Failed")
    Else
        If ws.Cells(r, "B") = password Then
            MsgBox ("Login OK")
            Sheets("MainSystem").Visible = xlSheetVisible
            Sheets("LoginSystem").Visible = xlSheetHidden
        Else
            MsgBox ("Login Failed")
        End If
    End If
End Sub