Data Validation From a User Form

[baseline9]

Following some advice i may of posted in the wrong place last time! This is a re-post.

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 THE CHALLENGE
If Range("A1:A1000").Value <> Me.acctnumber.Value Then
MsgBox "Please Enter Correct Account Number"
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.acctnumber.Value
ws.Cells(iRow, 2).Value = Me.action.Value
ws.Cells(iRow, 3).Value = Me.comments.Value
ws.Cells(iRow, 4).Value = Me.recheck.Value

'clear the data
Me.acctnumber.Value = ""
Me.action.Value = ""
Me.comments.Value = ""
Me.recheck.Value = ""
Me.acctnumber.SetFocus

Dim nResult As Long
nResult = MsgBox( _
Prompt:="Data Transfered, Do You Need To Enter Another Account?", _
Buttons:=vbYesNo)
If nResult = vbYes Then
Exit Sub
Else: Unload Me
End If

End Sub

Also i need to ref another work sheet?
Really hope you can help!
Mike.

 

[SkipVought]

Hi,

If Not IsError(Application.Match(Me.acctnumber.Value, Range("A1:A1000"),0)) Then[s]
  MsgBox "Please Enter Correct Account Number"[/s][b]
'Now go do good stuff with the acctnumber!!![/b]
End If

But WHY the message? You must already HAVE the account number, cuz you just tested it!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[baseline9]

The first IF statement ensures the user enters an account number so if its blank they get the MSG
"Please Enter Account Number"

I guess I was trying to use this code to then ensure the account number they entered is valid by checking it against a list of pre defined account numbers (Up to 12000)

IF the account number exists then the code continues

IF the account number does not exist then a MSG saying
"you have entered a wrong account number" or something along those lines
EG if they entered 123456 this is not in the list of my account numbers and therefore not valid.

Finally it would be useful to reference another worksheet
where the account list lives

Basically I'm trying to recreate the "Data Validation, List"
function that Excel already has, however it does not work from a useform.

Sorry to be a pain - hope you can help.

 

[SkipVought]

Use a ComboBox. It will assure a correct result every time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[baseline9]

There are over 12000 accounts. I don’t think that would be an option as it would take the user ages to find the number in the list.

Again apologies if I'm being a pain, really hope you can help!

 

[SkipVought]

If IsError(Application.Match(Me.acctnumber.Value, Range("A1:A1000"),0)) Then
  MsgBox "Please Enter Correct Account Number"
  Exit Sub
End If

'... now continue with the acctnumber

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mjpearson]

I'm new to this stuff but this looks odd to me:

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

Checking a range of accounts against s single value? Would it be better to use a VLOOKUP function instead?

Again, I'm new to this stuff but is there an IN or a MATCH function in VBA that you can use?

 

[baseline9]

Skip, your a legend! Works perfectly.

However there is a catch.
Because I was comparing text against value the code could not find the account numbers - no problem I have created a text version of the account numbers to look against.

Happy Days
Many Thanks.
Mike.