Consulting

Results 1 to 3 of 3

Thread: TextBox VLookUp not diplay message if EAN is wrong

  1. #1

    TextBox VLookUp not diplay message if EAN is wrong

    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
    Attached Files Attached Files

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    Very very good!!

    Thank you!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •