Data Validation From a User Form

[baseline9]

Hello to all! My first post.

I have been looking over the internet for some hours now but I cannot find a solution to a problem I have.

I'm trying to validate an account number that a user inputs into a text box on a userform (VB6).
This is by cross referencing against a list of pre-defined account numbers on a separate sheet in the workbook.
My code so far looks like this (I keep getting mis match 13)

Private Sub submit_Click()

Worksheets("Audit_Data").Select

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Audit_Data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a acct number
If Trim(Me.acctnumber.Value) = "" Then
Me.acctnumber.SetFocus
MsgBox "Please Enter Account Number"
Exit Sub
End If

' THIS IS MY CHALLENGE BIT!
If Range("A1:A1000").Value <> Me.acctnumber.Value Then
MsgBox "Please Enter Correct Account Number"
End If

Also the reference to a another sheet is not built in yet.

Really hope you can help!
Many thanks.
Mike.

 

[guitarzan]

You should repost in the VBA forum here: forum707

 

[SkipVought]

I'm trying to validate an account number that a user inputs into a text box on a userform (VB6).
This is by cross referencing against a list of pre-defined account numbers on a separate sheet in the workbook.

Why FORCE the user to KEY IN data. Waste of time for YOU and THEM!

Use a ComboBox!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}