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?
<tbody>
</tbody>
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 |
<tbody>
</tbody>