Run-Time Error 424

[K1600]

Can anyone help me find the issue in my code? It seems to work ok other than I keep getting a Run-Time Error 424. When I click 'Debug' it drops me on to this line
Loop While Not rngUser Is Nothing And firstUser <> rngUser.Address
The code is a username and password setup where it checks the username in another spreadsheet against the password in an adjacent column. The checking of the user name and password is working fine and lets me in to my next userform as planned but then when I come to exit I get the error.

Thanks in advance.

Private Sub CMD_Login_Click()

If TxtPIN.value = "" Then
MsgBox ("Enter your PIN number"), vbOKOnly
TxtPIN.SetFocus
Exit Sub
End If

If TxtPassword.value = "" Then
MsgBox ("Enter your password"), vbOKOnly
TxtPassword.SetFocus
Exit Sub
End If

Dim wbk As Workbook
Dim ws As Worksheet
Dim UserName As String, PW As String
Dim rngUser As Range
Dim firstUser As String
Dim UserFound As Boolean

Set wbk = Workbooks.Open("XXXX\Returns v.2.0.xlsx", ReadOnly:=True)
Sheets("Admin Users").Select
'Set ws = wbk.Sheets("Admin Users")

UserName = TxtPIN.value
PW = TxtPassword.value
With Range("A:B")
Set rngUser = .Find(UserName, lookat:=xlWhole)
If Not rngUser Is Nothing Then
firstUser = rngUser.Address
Do
If (PW = rngUser.Offset(0, 1).value) And rngUser.Offset(0, 6).value = "Yes" Then
UserFound = True
TxtUsers.Text = wbk.Sheets("Data").Range("AI2")
TxtTotalTests.Text = wbk.Sheets("Data").Range("AJ2")
wbk.Close
UsrFrmAdminDashboard.Show
Else
Set rngUser = .FindNext(rngUser)
End If
Loop While Not rngUser Is Nothing And firstUser <> rngUser.Address
Else
MsgBox "You are not authorised to use this system!", vbExclamation, "Returns - Admin Dashboard"
TxtPIN.value = ""
TxtPassword.value = ""
Exit Sub
End If
End With

If Not UserFound Then
MsgBox "Either your account access has not been approved or you have entered an incorrect password!", vbOKOnly, "Returns - Admin Dashboard"
TxtPIN.value = ""
TxtPassword.value = ""
TxtPIN.SetFocus
End If
End Sub

 

[dmt32]

Hi,
I know its been published in many places like that, but code structure is in my view, not incorrect

try updating your code as follows

Set rngUser = .FindNext(rngUser)
If rngUser Is Nothing Then Exit Do
End If
Loop While firstUser <> rngUser.Address

Dave

 

[K1600]

Thanks for the speedy response Dave. Pardon my poor knowledge here but where abouts in my current code am I putting the new code? I tried it in a couple of places that I thought it might be but I clearly haven't got it right.

 

[Fluff]

Will the username only occur once in the "Admin Users" sheet?

 

[K1600]

Yes, there is a function which will prevent the same username (which is a 5 digit code) being entered more than once.

 

[Fluff]

In that case there is no need for the loop, try

    Set wbk = Workbooks.Open("XXXX\Returns v.2.0.xlsx", ReadOnly:=True)
    
    UserName = TxtPIN.Value
    PW = TxtPassword.Value
    
    Set rngUser = wbk.Sheets("Admin Users").Range("A:B").Find(UserName, , , xlWhole, , , False, , False)
    If Not rngUser Is Nothing Then
        If (PW = rngUser.Offset(0, 1).Value) And rngUser.Offset(0, 6).Value = "Yes" Then
            TxtUsers.Text = wbk.Sheets("Data").Range("AI2")
            TxtTotalTests.Text = wbk.Sheets("Data").Range("AJ2")
            wbk.Close False
            UsrFrmAdminDashboard.Show
        Else
            MsgBox "Either your account access has not been approved or you have entered an incorrect password!", vbOKOnly, "Returns - Admin Dashboard"
            TxtPIN.Value = ""
            TxtPassword.Value = ""
            TxtPIN.SetFocus
            Exit Sub
        End If
    Else
        MsgBox "You are not authorised to use this system!", vbExclamation, "Returns - Admin Dashboard"
        TxtPIN.Value = ""
        TxtPassword.Value = ""
        Exit Sub
    End If
    wbk.Close False
End Sub

 

[K1600]

Cheers Fluff, I'll give it a whirl later or in the morning and report back.

 

[Fluff]

Ok, look forward to hearing the results.

 

[dmt32]

Thanks for the speedy response Dave. Pardon my poor knowledge here but where abouts in my current code am I putting the new code? I tried it in a couple of places that I thought it might be but I clearly haven't got it right.

Sorry late replay, attended family party last night - looks like you did not need the do loop in your code but as Fluff on case, sure you will now be sorted.

Happy New Year

Dave

 

[K1600]

No issues with the late reply Dave, thanks for your response.
Fluff: seems to be working great thanks.

Does anyone know if there is a way to stop the 'Downloading' and then 'Opening' progress bar box from showing when the code opens a workbook (image attached). The issues isn't that it shows what it is doing but that there is a cancel command button on it, which if pressed stops the macro from running. Thanks.