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

 

[NMa91]

Try this:

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
 Dim Inventory As String
 
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
 Inventory = Application.InputBox("Enter inventory count", "", , , , , , 1)
 For i = 1 To Inventory
    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
Next i
 End If
 Application.EnableEvents = False
 Target.Value = ""
 Application.EnableEvents = True
 Target.Select
 End Sub

 

[sumsaam]

inventory count should be optional not compulsory, controlled by checkbox, and there is and error when i pur quantity 6 it puts it in six lines in 1 each. please fix it

 

[FormR]

Hi, here is another option you can try, this assumes your check box is a forms control check box on the active sheet and it is called "Check Box 1".

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

 

[steve the fish]

Using your code you could do this:

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

If CheckBox1.Value = True Then
    myVal = InputBox("Please enter quantity")
    If Not IsNumeric(myVal) And Len(myVal) > 0 Then
        MsgBox "Enter a number! Rescan barcode"
        Exit Sub
    End If
Else
    myVal = 1
End If

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 + myVal
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 = myVal
End If

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

Target.Select

End Sub

You just need to add an activex checkbox to the sheet.

 

[sumsaam]

its working good but one there are bugs in quantity mode if i enter 1 and there is already quantity 1 it does not ad quantity it makes it 11 not 2. it happenens an item with barcode "789" . and one item having barcode "item1" goes right

 

[FormR]

its working good but one there are bugs in quantity mode if i enter 1 and there is already quantity 1 it does not ad quantity it makes it 11 not 2. it happenens an item with barcode "789" . and one item having barcode "item1" goes right

Hi, you have had more than one suggestion - to which are you referring to?

 

[NMa91]

Thanks for clarifying. Check this:

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
 Dim Inventory As String
 
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
 Inventory = 1
If Me.chkQty.Value Then Inventory = Application.InputBox("Enter inventory count", "", , , , , , 1)
 
    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 = Inventory
 End If
 Application.EnableEvents = False
 Target.Value = ""
 Application.EnableEvents = True
 Target.Select
 End Sub

 

[sumsaam]

Last One

 

[NMa91]

Please add checkbox(chkQty) to worksheet. I mentioned reference to it in code.