PDA

View Full Version : [SOLVED] TextBox VLookUp not diplay message if EAN is wrong



elsg
08-21-2015, 07:18 AM
I have EAN in tab (BD_Prod) and i have UserForm (frmCadAva), when i enter correct EAN in txtEan work well, but i clear and enter wrong EAN not displaymessage.

I need VLookUp (inside Userform), run all time to verify correct or worng entered on textbox (txtEan).


Private Sub txtEan_Exit(ByVal Cancel As MSForms.ReturnBoolean)If Len(txtEan.Text) >= 7 Then
On Error Resume Next
form1 = WorksheetFunction.VLookup(Val(txtEan.Text), Worksheets("BD_Prod").Range("A1:D100000"), 2, 0)
form2 = WorksheetFunction.VLookup(Val(txtEan.Text), Worksheets("BD_Prod").Range("A1:D100000"), 3, 0)
form3 = WorksheetFunction.VLookup(Val(txtEan.Text), Worksheets("BD_Prod").Range("A1:D100000"), 4, 0)
If Err.Number = 0 Then
txtCod.Text = form1
txtDesc.Text = form2
txtVlrUnit.Text = form3
Else
txtEan.Text = "EAN not found"
On Error Resume Next
End If
On Error GoTo 0
End If
Me.txtEan.BackColor = RGB(255, 255, 255)
End Sub

mancubus
08-22-2015, 08:54 AM
i am not sure i understand your problem.



Private Sub txtEan_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On Error Resume Next

If Len(txtEan.Text) >= 7 Then
form1 = WorksheetFunction.VLookup(Val(txtEan.Text), Worksheets("BD_Prod").Range("A1:D100000"), 2, 0)
form2 = WorksheetFunction.VLookup(Val(txtEan.Text), Worksheets("BD_Prod").Range("A1:D100000"), 3, 0)
form3 = WorksheetFunction.VLookup(Val(txtEan.Text), Worksheets("BD_Prod").Range("A1:D100000"), 4, 0)
If Err.Number = 0 Then
txtCod.Text = form1
txtDesc.Text = form2
txtVlrUnit.Text = form3
Else
MsgBox "Please enter correct EAN number", vbOKOnly, "EAN not found"
txtEan.SetFocus
txtEan.Text = ""
txtCod.Text = ""
txtDesc.Text = ""
txtVlrUnit.Text = ""
End If
Else
MsgBox "Please enter correct EAN number", vbOKOnly, "EAN not found"
txtEan.SetFocus
txtEan.Text = ""
txtCod.Text = ""
txtDesc.Text = ""
txtVlrUnit.Text = ""
End If

Me.txtEan.BackColor = RGB(255, 255, 255)

End Sub

elsg
08-24-2015, 05:48 AM
Very very good!!

Thank you!!!!