Barcode scanner to check goods in and sold stock

[Dinkor]

Hi everyone,

I'm pretty new to excel and am looking to increase my knowledge and the functionality that the programme can provide me.

If I paint you a brief picture of what I do and what I would like to do it may help.

I have products that I outsource QC checking on. I send lots of used devices to a third party and then recieve the devices back along with a spreadsheet. The spreadsheet will include a "SN" (Serial number) which is also applied to the box of the physical item with a corresponding barcode. I then add columns to the sheet the third party provides to help me track the profitability of the sales (usually on eBay).

What I would like to do...

1: I would like to use a barcode scanner to recieve the goods in. I would like to open the sheet, then use the barcode scanner to scan the barcode on a physical box. This scan would locate the item on the excel sheet and then colour the SN cell (green) to indicate that the item has physically been recieved. It would then wait for me to scan the next code and do the same until all physical items had been scanned in against the provided sheet. This would enable me to check if all the advised units were recieved.

2: Once a sale is made i would open the sheet (or a duplicate of the goods in sheet (goods out)) select a physical item and scan it out with SN barcode. It would then change the colour of scanned the SN (to orange) to indicate the item has gone and move the cell to column (K "sales ref" same row as SN scanned) where i could manually populate the eBay sales reference in that cell; the sell price (in L) and fees (in N). It would be good if everytime a Barcode scan input was made that excel would know to return to column B and lookup/start sequence.

If anyone has the time to help me figure out a way to do this it would be greatly appreicated. I am not sure if excel would need a plug in or similar to enable this sort of functionality.

Many Thanks

Dinkor

[torachan]

see if the attached approach works for you - with your scanner set 'CR' - first scan colours the cell - second scan resets to no fill - if there is little light reflection you can trial by scanning the barcode on screen.
for it to work the userform has to be present.

[Dinkor]

Many thanks Torachan,

This does the first part perfectly and assistsd in the second part by selecting the cell. I'm now trying to understand what you've done and how you did it and why certain steps are necessary.

Thank you for helping me on my excel adventure!

o7

[torachan]

updated file has the same data on sheet2 - the userform2 has the facility to fill in the missing data - the code should be self explanatory - the only thing that is not visible is the textbox that receives the scan signal - this is in the upper left corner of the form and its size and position are all set to zero - so it is present and focus is set onto it.

[Dinkor]

Thank you very much for taking the time to help.

I'm a little pushed for time today but will delve into it as soon as I can.

Appreciate it.