amendment wanted,

[sumsaam]

Hi,
I got this code by searching on internet, and i use to count my store inventory by scanning there barcodes, We scan Barcodes in H1 cell, it searches that if barcodes list i pasted having the scanned barcode it increases the quantity of that item, if we scan a new barcode(not in pasted barcode list), it puts it at end of my list.
I facing a problem, mostly during counting i have to scan 10 or 20 pcs of same barcode is very time taking,
I want that a check box in excel sheet labeled "quantity mode". if i check the box "quantity mode" it should appear a box prompting for quantity after scanning a barcode, and if i uncheck the box it should take one scan as one pcs automatically.

the code i here


Private Sub Worksheet_Change(ByVal Target As Range)

Const SCAN_CELL As String = "H1"
Const RANGE_BC As String = "A2:A5000"
Dim val, f As Range, rngCodes As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

val = Trim(Target.Value)
If Len(val) = 0 Then Exit Sub

Set rngCodes = Me.Range(RANGE_BC)

Set f = rngCodes.Find(val, , xlValues, xlWhole)
If Not f Is Nothing Then
With f.Offset(0, 2)
.Value = .Value + 1
End With
Else
Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
f.Value = val
f.Offset(0, 1).Value = "enter description"
f.Offset(0, 2).Value = 1
End If

Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True

Target.Select

End Sub

 

[FormR]

Last One

Maybe you want to try and comment on the other suggestions, including the latest from NMa91 also?

 

[sumsaam]

FormR's code is working more accurately. but also have a little bug. in quantity mode when we click ok blank quantity it gives formula error

just do that qty box shoul appear with defaulty qty 1. supose we just press enter at qty prompt it should take qty 1

 

[steve the fish]

I looked at my code. Cant replicate what you say and does what you asked for it seems.

 

[sumsaam]

No No FormR your code is most stable and less bug
i m talking about your code in which "checkbox" font colored red

 

[sumsaam]

sorry steve i didnt try your code. i was busy
wait i try

 

[sumsaam]

steve's code and FormR's codes are behaving same, if i leave blank qty field it gives error, default qty should 1. and if i cancel blank qty box, it also gives error

 

[FormR]

default qty should 1.

In my suggestion, try changing this line as below:

If QtyMode Then qty = Application.InputBox("Enter the qty", Default:=1, Type:=1)

 

[sumsaam]

just add one thing that qty box should appear with 1 default qty

 

[sumsaam]

its allright thaaaaaaaaaaaaaaaaaaaaaannnnnnnnnnnnnnxxxxxxxxxxxxxxxxxxx

 

[sumsaam]

Hi formR,
you gave me a code to improve my vba module for barcode scanning, i want some more little improvements, can you help me? thank you

1- i want the scaned barcode in input box's caption , it should appear as Enter the Qty of (scanned barcode). For example "Enter the qty of Item1"

2- If a scanned item is not in pasted list it goes in end of data, i want it that if an out of list item is scanned it should appear a box titled "out of List" and having two buttons 1- "go to end" and 2- "Scan again". "go to end" will paste this item at end as it already does, and "Scan again " will clear the cell H1 and go again in H1 . thank you



the code is


Private Sub Worksheet_Change(ByVal Target As Range)


Const SCAN_CELL As String = "H1"
Const RANGE_BC As String = "A2:A5000"
Dim val, f As Range, rngCodes As Range, qty As Variant, QtyMode As Boolean


If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

val = Trim(Target.Value)
If Len(val) = 0 Then Exit Sub


QtyMode = Me.Shapes("Check Box 1").OLEFormat.Object.Value = 1


'If QtyMode Then qty = Application.InputBox("Enter the qty", Type:=1)
If QtyMode Then qty = Application.InputBox(prompt:="Enter the Qty", Title:="Quantity box", Default:=1, Type:=1)

If Not qty Then
Set rngCodes = Me.Range(RANGE_BC)
Set f = rngCodes.Find(val, , xlValues, xlWhole)
If Not f Is Nothing Then
With f.Offset(0, 2)
.Value = .Value + IIf(QtyMode, qty, 1)
End With
Else
Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
f.Value = val
f.Offset(0, 1).Value = "enter description"
f.Offset(0, 2).Value = IIf(QtyMode, qty, 1)
End If
End If


Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True


Target.Select


End Sub