Solved: VBA VLOOKUP error

[Glaswegian]

Hi again

I have a password form for users to input a staff number and a password. I'm trying to use a VLOOKUP to check the staff number but it constantly errors out. The staff number will be a combination of letters and numbers.

Private Sub cmbOK_Click()
Dim IDno
Dim myRng As Range
Set myRng = Sheets("Sheet3").Range("U3:V5")
IDno = Application.WorksheetFunction.VLookup(Me.txbIDno.Value, myRng, 2, False)
If IDno = "#N/A" Then
MsgBox "Invalid ID Number - please try again.", vbInformation + vbOKOnly, "Warning"
Unload frmPassword
End If
If txbPass.Text = "password" Then
UnlockForms
Else
MsgBox "Invalid Password - please try again.", vbInformation + vbOKOnly, "Warning"
Unload frmPassword
End If
End Sub
For the life of me I can't see where I'm going wrong :banghead: .

No doubt something simple - thanks for any help.

[Bob Phillips]

Untested,



Private Sub cmbOK_Click()
Dim IDno As String
Dim myRng As Range

Set myRng = Sheets("Sheet3").Range("U3:V5")
On Error Resume Next
IDno = Application.WorksheetFunction.VLookup(Me.txbIDno.Value, myRng, 2, False)
On Error GoTo 0
If IDno = "" Then
MsgBox "Invalid ID Number - please try again.", vbInformation + vbOKOnly, "Warning"
Unload frmPassword
ElseIf txbPass.Text = "password" Then
UnlockForms
Else
MsgBox "Invalid Password - please try again.", vbInformation + vbOKOnly, "Warning"
Unload frmPassword
End If
End Sub

[Glaswegian]

Thanks again.

Text box = string value - simple really....