VBA Code for excel inventory management using bar-code scanning

Question

I have little experience with VBA code. I have an excel spreadsheet for inventory tracking and I am trying to set up a way to use a bar-code scanner to automatically search for the scanned number inside the spreadsheet and prompt the user to add or remove a number of associated items.

Can anyone provide me with help in writing a code that would be able to do this?

The goal here would be that after the scan of the item bar-code in B1, the spreadsheet would search for the associated scanned number in Column C, and then prompt you to update the associated "Units in Stock" number in Column E.

After the operation is complete, B1 would then turn blank again, ready for the next scan.

Any help would be greatly appreciated!

Are you saying that the physical bar code would be in cell B1? Or are you scanning the bar code and having the associated scanned number placed in cell B1? — TheEngineer
– TheEngineer, Commented Dec 30, 2014 at 18:30

Kartoch · Accepted Answer · 2014-12-30 19:59:32Z

1

Sub barcode() Dim barcodeval As String Dim i As Integer Dim j As Integer Dim quantity As Integer i = 1 j = 3 barcodeval = Cells(1, 2) Do While Cells(i, j) <> 0 If barcodeval = Cells(i, j) Then quantity = InputBox("Update number of units in stock", "Update", "Enter new value here") Cells(i, j + 2) = quantity i = i + 1 Else i = i + 1 End If Loop End Sub

edited Dec 30, 2014 at 19:59

Kartoch

7,82910 gold badges44 silver badges69 bronze badges

answered Dec 30, 2014 at 19:52

John

111 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

barrowc Over a year ago

Admittedly the question does not specify whether duplicate scanned numbers exist in column C but I think breaking out of the Do loop after matching is a more likely scenario. Just need to tie this into a Worksheet_Change event, deal with the user potentially cancelling the InputBox and end by resetting the focus back to B1. Might also be useful to display the pre-existing number of units in stock as part of the InputBox message but again this is not specified in the question

John Over a year ago

Yes that's true. To be honest I did this very rushed I would probably search through by declaring and initializing a range object. Question did not specify much but I can edit it if necessary

Collectives™ on Stack Overflow

VBA Code for excel inventory management using bar-code scanning

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related