Results 1 to 3 of 3

Thread: Search manually from sub_Change
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search manually from sub_Change

    Hello,

    I have a userform with a lot of textboxes, one of these textboxes is "txtBarCode" that allows me to enter any Barcode to check if the item is exist or not and lookup values for "txtItemName" & "txtPrice" and I have the code below that works very well
    But, sometimes I need to enter the Barcode number manually
    With my code below, If I entered "1" it will get the values of "B2" & "C2"
    But if I need to search for the barcode "12" it will get the value of "B2" & "C2" and then "B3" & "C3" (Because the lookup values change with every change I made)

    Is there any way to make this code works with 2 entry ways, Barcode Scanner & manually?

    Code:
    Private Sub txtBarCode_Change()
    Dim ws As Worksheet
    Set ws = Sheet2
    On Error Resume Next
    If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.txtBarCode.Value) = 0 Then
    MsgBox "This item is not exist"
    Me.txtBarCode.Value = ""
    Exit Sub
    End If
    With Me
    .txtItemName = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 2, 0)
    .txtPrice = Format(Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 4, 0), "#,##0")
    nr = ws.Cells(Rows.Count, "P").End(xlUp).Row + 1
    ws.Cells(nr, "T") = Sheet2.Cells(Rows.Count, "T").End(xlUp).Value + 1
    ws.Cells(nr, "U") = CDbl(Me.txtBarCode)
    ws.Cells(nr, "V") = Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 2, 0)
    ws.Cells(nr, "W") = CDbl(Format(Application.WorksheetFunction.VLookup(CLng(Me.txtBarCode), Sheet1.Range("A4").CurrentRegion, 4, 0), "#,##0"))
    txtBarCode = "" 'because this line of code I added below lines of code
    Call txtAmount_Change
    Me.txtBarCode.SetFocus
    End With
    End Sub

    A B C
    1 Barcode Item Name Price
    2 1 Test 1000
    3 2 Test2 2000
    4 123 Test3 3000

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    802
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search manually from sub_Change

    Try using _AfterUpdate or _Exit instead of _Change

  3. #3
    Board Regular
    Join Date
    Jun 2016
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search manually from sub_Change

    If there is no solution for what I need then will use AfterUpdate

    But I need to get the values directly because I may enter 100 items in one invoice, and it's easy and faster if the event is _Change

    What I need is to use my form as cashier

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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