Search manually from sub_Change

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
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


[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]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top