Using barcode scanner to decrease quantity

[JoeL5]

Hi, I am trying to use a barcode scanner to amend the value of an item in excel - each time the item is removed, its corresponding barcode will be scanned. I want to make scanning the barcode automatically decrease the quantity in an excel sheet to make it show that it has been removed. I have looked a bit at VBA and command buttons, but am struggling to work out if excel has a way to use barcode scanners in the same way as a command button if that makes sense? Thank you.

 

[JoeL5]

AI Solution 5



Sub MyMacro()

Dim i As Integer

i = 1

Do While i <= 10 'change 10 to the number of times you want the macro to run[LJ(CP1]

' Prompt user to scan barcode

Dim barcode As String

barcode = InputBox("Scan barcode:")



' Check if user wants to stop the macro

If barcode = "stop" Then

End ' Stop macro execution

End If[LJ(CP2]



' Find matching item in column A[LJ(CP3]

Dim match As Range

Set match = Columns("A").Find(What:=barcode, LookIn:=xlValues, LookAt:=xlWhole)



' If match found, reduce quantity in column B[LJ(CP4] by 1

If Not match Is Nothing Then

Dim quantity As Integer

quantity = match.Offset(0, 1).Value

If quantity > 0 Then

match.Offset(0, 1).Value = quantity - 1

Else

MsgBox "Item out of stock"

End If

Else

MsgBox "Item not found"

End If

i = i + 1

Loop

End Sub

---

[LJ(CP1]Change to 999999
[LJ(CP2]Code 6. Stops Macro running when 'stop' entered into scanner box.
[LJ(CP3]Controls barcode column
[LJ(CP4]Controls quantity column