Barcode Notification System

[NickRed18]

Hi all,

I work for a small manufacturing company, and I have been tasked with setting up a barcode system. I am not trying to setup an inventory management system, I do not need to keep track of any quantities (or at least as of right now). I could see the system wanting to be expanded into keeping track of inventory and maybe even similar to an ERP system, but a simple setup will work just fine for now.
I've researched some about barcodes and how they work with excel. Like scanning a barcode just acts as if the user typed in the actual number (and hit enter? maybe, I am not sure). There's a couple other things I've learned, but I have zero real experience with them.

Basically, what I need help with is creating a notification system associated with each of our part numbers (around 150-200 parts in total). The end goal of this project is to have a TV/display of a matrix of all/a majority of the parts (maybe just by casting a desktop's screen?) with a color coding system to show the status of the part.

Green = in stock with a decent supply
Red = Out of stock/Nearing out of stock
Yellow = Part has been ordered and is on the way (possibly display an expected delivery date)

The employee in the inventory section will notice the part is getting low on its supply, and will scan the barcode for that part number. The TV will turn that part number Red showing it needs ordering. The sales team will notice the Red part number on the screen, and they will place an order for it. They will scan their barcode for that part number, and the TV will turn Yellow for that part number. Once the part is delivered, the receiving team will scan their barcode for that part number, and the TV will turn Green for that part number.

The part that is giving me trouble is how exactly a barcode scanner interacts with excel.
Whenever it scans a code, can you assign a certain cell the value is pasted into?
Do you program the barcode scanner about which workbook to use, or is that done in excel?
Can you have 2 different barcode scanners, that when scanning the same barcode they perform different tasks (turn the part number to Red, and then turn the part number to yellow, etc.)?

I am by no means an expert with VBA, but I have some experience and I feel I could figure out a solution if given some guidance.


Thank you for your time,

Nick

 

[NickRed18]

This is the master-list that associates the part numbers with their barcode and status. We will create our own barcodes with a barcode printer, and dedicate each part number to a barcode.

Status:
0 = Green, in-stock
2 = Red, out of stock
1 = Yellow, ordered and on its way


[TABLE="width: 297"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Barcode[/TD]
[TD]Part number[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]7933644[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5000405[/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6776680[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5879563[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1484240[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9488684[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9136108[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2511998[/TD]
[TD="align: right"]108[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3228163[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1458317[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3541267[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]etc.......[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

There will be a tv display showing all the part numbers in a grid (all the 3-digit numbers below are part numbers). Since all the part numbers have a status of 0, they would all show up as green on the display. Whenever someone scans a certain barcode, the status for that part number changes. So whenever the inventory employee scans their barcode for part (say, 110) because it is low in stock, the display (below), will turn red for part number 110. Same for the sale associate whenever they order part 110, they scan their barcode and the display will be yellow for part number 110.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]101[/TD]
[TD="class: xl65, width: 64"]117[/TD]
[TD="class: xl65, width: 64"]133[/TD]
[TD="class: xl65, width: 64"]149[/TD]
[TD="class: xl65, width: 64"]165[/TD]
[TD="class: xl65, width: 64"]181[/TD]
[TD="class: xl65, width: 64"]197[/TD]
[/TR]
[TR]
[TD="class: xl65"]102[/TD]
[TD="class: xl65"]118[/TD]
[TD="class: xl65"]134[/TD]
[TD="class: xl65"]150[/TD]
[TD="class: xl65"]166[/TD]
[TD="class: xl65"]182[/TD]
[TD="class: xl65"]198[/TD]
[/TR]
[TR]
[TD="class: xl65"]103[/TD]
[TD="class: xl65"]119[/TD]
[TD="class: xl65"]135[/TD]
[TD="class: xl65"]151[/TD]
[TD="class: xl65"]167[/TD]
[TD="class: xl65"]183[/TD]
[TD="class: xl65"]199[/TD]
[/TR]
[TR]
[TD="class: xl65"]104[/TD]
[TD="class: xl65"]120[/TD]
[TD="class: xl65"]136[/TD]
[TD="class: xl65"]152[/TD]
[TD="class: xl65"]168[/TD]
[TD="class: xl65"]184[/TD]
[TD="class: xl65"]200[/TD]
[/TR]
[TR]
[TD="class: xl65"]105[/TD]
[TD="class: xl65"]121[/TD]
[TD="class: xl65"]137[/TD]
[TD="class: xl65"]153[/TD]
[TD="class: xl65"]169[/TD]
[TD="class: xl65"]185[/TD]
[TD="class: xl65"]201[/TD]
[/TR]
[TR]
[TD="class: xl65"]106[/TD]
[TD="class: xl65"]122[/TD]
[TD="class: xl65"]138[/TD]
[TD="class: xl65"]154[/TD]
[TD="class: xl65"]170[/TD]
[TD="class: xl65"]186[/TD]
[TD="class: xl65"]202[/TD]
[/TR]
[TR]
[TD="class: xl65"]107[/TD]
[TD="class: xl65"]123[/TD]
[TD="class: xl65"]139[/TD]
[TD="class: xl65"]155[/TD]
[TD="class: xl65"]171[/TD]
[TD="class: xl65"]187[/TD]
[TD="class: xl65"]203[/TD]
[/TR]
[TR]
[TD="class: xl65"]108[/TD]
[TD="class: xl65"]124[/TD]
[TD="class: xl65"]140[/TD]
[TD="class: xl65"]156[/TD]
[TD="class: xl65"]172[/TD]
[TD="class: xl65"]188[/TD]
[TD="class: xl65"]204[/TD]
[/TR]
[TR]
[TD="class: xl65"]109[/TD]
[TD="class: xl65"]125[/TD]
[TD="class: xl65"]141[/TD]
[TD="class: xl65"]157[/TD]
[TD="class: xl65"]173[/TD]
[TD="class: xl65"]189[/TD]
[TD="class: xl65"]205[/TD]
[/TR]
[TR]
[TD="class: xl65"]110[/TD]
[TD="class: xl65"]126[/TD]
[TD="class: xl65"]142[/TD]
[TD="class: xl65"]158[/TD]
[TD="class: xl65"]174[/TD]
[TD="class: xl65"]190[/TD]
[TD="class: xl65"]206[/TD]
[/TR]
[TR]
[TD="class: xl65"]111[/TD]
[TD="class: xl65"]127[/TD]
[TD="class: xl65"]143[/TD]
[TD="class: xl65"]159[/TD]
[TD="class: xl65"]175[/TD]
[TD="class: xl65"]191[/TD]
[TD="class: xl65"]207[/TD]
[/TR]
[TR]
[TD="class: xl65"]112[/TD]
[TD="class: xl65"]128[/TD]
[TD="class: xl65"]144[/TD]
[TD="class: xl65"]160[/TD]
[TD="class: xl65"]176[/TD]
[TD="class: xl65"]192[/TD]
[TD="class: xl65"]208[/TD]
[/TR]
[TR]
[TD="class: xl65"]113[/TD]
[TD="class: xl65"]129[/TD]
[TD="class: xl65"]145[/TD]
[TD="class: xl65"]161[/TD]
[TD="class: xl65"]177[/TD]
[TD="class: xl65"]193[/TD]
[TD="class: xl65"]209[/TD]
[/TR]
[TR]
[TD="class: xl65"]114[/TD]
[TD="class: xl65"]130[/TD]
[TD="class: xl65"]146[/TD]
[TD="class: xl65"]162[/TD]
[TD="class: xl65"]178[/TD]
[TD="class: xl65"]194[/TD]
[TD="class: xl65"]210[/TD]
[/TR]
[TR]
[TD="class: xl65"]115[/TD]
[TD="class: xl65"]131[/TD]
[TD="class: xl65"]147[/TD]
[TD="class: xl65"]163[/TD]
[TD="class: xl65"]179[/TD]
[TD="class: xl65"]195[/TD]
[TD="class: xl65"]211[/TD]
[/TR]
[TR]
[TD="class: xl65"]116[/TD]
[TD="class: xl65"]132[/TD]
[TD="class: xl65"]148[/TD]
[TD="class: xl65"]164[/TD]
[TD="class: xl65"]180[/TD]
[TD="class: xl65"]196[/TD]
[TD="class: xl65"]212[/TD]
[/TR]
</tbody>[/TABLE]


I'm starting to get an idea on how this can work just by explaining it, so thank you for that.
But, what I had in mind about the code is whenever someone scans a barcode, that code is inserted into cell A1. There will be some kind of Vlookup type search (I cannot remember the function in VBA to search a range of cells, is it .Find?) on the master list to find what part number is associated with the barcode just inserted into A1 (again, from scanning the barcode). That part seems simple enough, and I could just have 3 barcodes per part number (one for each status - 0, 1, 2) no problem.

There is a designated computer solely for inventory, so I could just always have the workbook open to constantly have the worksheets updated. Do you know how I could setup a barcode scanner to only scan to a certain cell? I have a couple more questions about scanners as well, but they do not necessarily need answering yet. I apologize for the long post, but it helps me to type everything out to fully think through it. Let me know if you see any issues in my plan.

- Nick

 

[NickRed18]

I never even thought about using Access for this. I can see how it would be the next step in an inventory system. I am pitching the idea of a simple color-based notification system tomorrow. I'll show how one could be created for a significantly lower cost, and then one that is an all in one service from another company.
If we do decide to make our own I would really appreciate the help exploring Access as a possibility. I'll give you an update sometime tomorrow or Thursday.

Thanks,
Nick

 

[EFANYoutube]

I will make a simple one in excel for you to use if you like and then you can explore access later

 

[SpillerBD]

SpillerBD,

I'm having a difficult time understand exactly what you're saying.
Are you saying that different bar-code types have certain numerals included in the code to distinguish them from other types? And that excel may or may not register the check-num, or that the physical barcode may not include the check-num where excel will still read it?
If I am understanding you correctly then thank you for the tip, I had no idea. That would have caused some issues had I not been informed.

1. Yes.
2. Excel will register the check-num that are included in that type of bar code. Excel will not perform the check and the those check numbers would not normally be included in bar-code specific reading systems.

Sounds like you see the potential complications and awareness is essential.
A lot of solutions just involve using your own bar-codes in place of the UPC codes that may already exist or recording the UPC code by reading the bar code with the reader, but knowing the read may show more numerals than show on the UPC.

 

[NickRed18]

EFANYoutube,

That would be incredible! My thoughts exactly on starting off in excel to see the limitations, but it looks like this system will not need to be anything more than a Status board.
If you want, we could collaborate whenever you start the VBA code. It's been a while since I've used it like I said before, and it would probably be a good idea to get refreshed on that.

 

[NickRed18]

We may plan on using vendor's shipping barcodes. Could this be overcome by simply performing a test-scan to see if there are any check-nums that excel registers, and then altering the barcode to match?

 

[EFANYoutube]

Ok update.
I have decided to give you one in Access because the share file function in excel is dodgy, I am about half way through and will post a link to the file when complete.
If your barcode reader gives you numbers in excel when you scan then the barcodes are fine to use

 

[EFANYoutube]

Ok so it's done
https://1drv.ms/f/s!AhE4tgp4eiiDllyFWUrtj2NIQVhD
Instructions
Put the back end in a shared folder accessible by everyone.
Give everyone a copy of the front end
Relink everyone's frontend to the back end through table link manager
Watch the magic.
https://1drv.ms/f/s!AhE4tgp4eiiDllyFWUrtj2NIQVhD
First set up the status' the way you want them
Then you will have to add all your items through the inventory button
There is a log table that you can check by pressing F11
Have fun

 

[NickRed18]

We actually do not have Access.

 

[NickRed18]

SpillerBD,

I'm currently looking for a cheap 1-D scanner to test my excel code. Do you have an advice on what I am looking for?
I've found some in the $5-$20 range, but will these essentially interact the same way with excel as the $150-$250 ones? Also, what about barcode languages. I've heard Code 39 and 128 are simple, common, and sometimes free, but will I easily be able to generate a code and print them on my label printer?

Thanks for the help on this thread,
Nick