Hi,

Trying to check if data entered in TextBox1 is a valid entry by looking
up a database and returning data into the userform. The "testid' is the
first column in the database and is sorted.

My code is as follows and I continue to receive the error message even
if the data entered in TextBox1 is valid.

Can anyone help please.


Private Sub TextBox1_AfterUpdate()

Sheets("Menu").Select
Range("Testid") = TextBox1.Value

If Range("Testid").FormulaR1C1 =
"=VLOOKUP(TextBox1.Value,TestDB,1,FALSE))" = False Then
MsgBox "Test ID does not exist! Either correct the number entered
or create a new Database record", vbOK, "Error message"
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox1.SetFocus

Exit Sub

Else
Range("Number").FormulaR1C1 =
"=if(testid="""","""",VLOOKUP(testID,testDB,2,FALS E))"
Range("Surname").FormulaR1C1 =
"=if(testid="""","""",VLOOKUP(testID,testDB,3,FALS E))"
Range("FirstName").FormulaR1C1 =
"=If(testid="""","""",VLOOKUP(testID,testDB,4,FALS E))"
End If

End Sub


--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=375335

I think that this'll get you started again...

Private Sub TextBox1_AfterUpdate()
Dim res as variant

res = application.match(textbox1.value,worksheets("menu" ).range("testid"),0)

if iserror(res) then
msgbox "Not there!
'clean up
else
'do the rest
end if

exit sub


BernzG wrote:

Hi,

Trying to check if data entered in TextBox1 is a valid entry by looking
up a database and returning data into the userform. The "testid' is the
first column in the database and is sorted.

My code is as follows and I continue to receive the error message even
if the data entered in TextBox1 is valid.

Can anyone help please.

Private Sub TextBox1_AfterUpdate()

Sheets("Menu").Select
Range("Testid") = TextBox1.Value

If Range("Testid").FormulaR1C1 =
"=VLOOKUP(TextBox1.Value,TestDB,1,FALSE))" = False Then
MsgBox "Test ID does not exist! Either correct the number entered
or create a new Database record", vbOK, "Error message"
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox1.SetFocus

Exit Sub

Else
Range("Number").FormulaR1C1 =
"=if(testid="""","""",VLOOKUP(testID,testDB,2,FALS E))"
Range("Surname").FormulaR1C1 =
"=if(testid="""","""",VLOOKUP(testID,testDB,3,FALS E))"
Range("FirstName").FormulaR1C1 =
"=If(testid="""","""",VLOOKUP(testID,testDB,4,FALS E))"
End If

End Sub

--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=375335

--

Dave Peterson

Hi dave,

Thanks for this but I could not get it to work.

I made a couple of changes and still getting the error message each
time when data is valid.

Private Sub TextBox1_AfterUpdate()
Dim res as variant

res =
application.match(textbox1.value,worksheets("testD B").range("A:A"),0)

if iserror(res) then
msgbox "Not there!
'clean up
else
'do the rest
end if

exit sub

What I am trying to do is validate the first field value in the form
(TextBox1) and lookup another worksheet named TESTDB Column A that has
a list of all the valid values. If not found then a rejection message.


Cheers,
Bernz


--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=375335

Hi Dave,

Found the problem and your code now works successfully.

The problem was that the TextBox1 field had a "text value" but the
lookup table was numeric and thats why I kept getting the error
message.

Thanks again anyway.

Cheers,
Bernz


--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=375335

Glad you got it.

BernzG wrote:

Hi Dave,

Found the problem and your code now works successfully.

The problem was that the TextBox1 field had a "text value" but the
lookup table was numeric and thats why I kept getting the error
message.

Thanks again anyway.

Cheers,
Bernz

--
BernzG
------------------------------------------------------------------------
BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
View this thread: http://www.excelforum.com/showthread...hreadid=375335

--

Dave Peterson