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?
[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Barcode[/TD]
[TD]Item Name[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Test[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]123[/TD]
[TD]Test3[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Barcode[/TD]
[TD]Item Name[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Test[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]123[/TD]
[TD]Test3[/TD]
[TD]3000[/TD]
[/TR]
</tbody>[/TABLE]