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

 

[EFANYoutube]

Hi Nick
Sounds like the task ahead is a big one and especially if you don't have VBA experience
First you need to realise that scanning barcodes is exactly like you said "Like scanning a barcode just acts as if the user typed in the actual number". the barcode can be set up to have an ENTER press at the end or not.
So just like someone is typing in a value it needs to be captured in some way in order to know what it is and what it is for.
there are several ways to do it:
User forms (Not if you don't have a lot of VBA experience)
Sheets with buttons (Again you would need some VBA experience)
Formula based, very little VBA experience only to help direct the team member to scan the code into the right cell and to update a number in the barcode register sheet

I personally would suggest learning some VBA and asking smaller questions on the MrExcel forums as you go along, also i would try to have the workbook set up so that you can introduce stock levels and mix min values later, the colour coding can all be done by conditional formatting

If you learn some VBA you would be able to do things like email a team member in a dept telling them that some stock is ready for order etc

Another suggestion is to look at doing this in access as you would be able to keep many more records meaning that you could keep an activity log of who ordered what and when etc

Hope this helps somewhat

Josh

 

[NickRed18]

Josh,

Thanks for the reply! You've given me some insight on what tasks lie ahead.
I have actually taken a college course over VBA and still have all my work saved, so what I meant by not being an expert is that it would require me to "freshen" up on what I've learned.

I had many assignments using User forms in that course, so I am confident I could create one. My main concern is creating that User form in a fool-proof way.
In an ideal world the inventory employee would only scan (to change the display to red) if they were low on parts, but what if they made a mistake. They would require the ability to change the display back to green. Small instances like this that are rare and abnormal will (I can guarantee a 100% chance) lead to the part status display being incorrect for a majority of the parts due to people getting sidetracked and distracted.

I've seen barcode scanners referred to as Mobile Computers where they could be setup to perform a wide variety of tasks. My vision is the user scans a barcode, and they have 3 options they can choose from:
1. This part is low in Inventory (turns the status red)
2. This part has been ordered (turns the status yellow)
3. This part has arrived in receiving (turns the status green)

I can understand using a more advanced tool will require a more advanced software/setup. How compatible are these with excel? What is the likelihood the required code could be created in a reasonable amount of time (1-2 weeks)? I don't have a gun to my head telling me to create this system from scratch or you're fired, but it would obviously be better to save the money from having to buy an existing service.

Thank you Josh and anyone else for the help,
Nick

 

[L. Howard]

How about posting a small exact example of your of how your inventory is set up on a sheet.
What gets scanned into what cell, and what exactly (cells, range, the part number...?) is to be turned the different colors.

Probably want a method to UN-color those items some way also?

Howard

 

[SpillerBD]

You've likely already seen this, but there are different bar-codes in use. Most barcodes in use for retail use check-nums to help validate the accuracy of the code read. Sometimes the check code numerals are included and sometimes not on the label. That is one thing that can give an Excel environment some issue. You maybe able to find an appropriate add-in for these, otherwise you will get the an un-validated read with out the check-sum which could easily allow errors.

 

[NickRed18]

For some reason MrExcel HtmlMaker and Forum Tools Add-in are crashing my excel. And I am not an administrator on my computer to use Excel Jeanie.

I could even show what I am trying to achieve with a screenshot but I cannot post one of those either. Any other suggestions on posting an example?

 

[NickRed18]

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.

 

[L. Howard]

I believe you can select a small sample on your sheet, then do All Borders on the selection, then copy and paste in your post.
Add the instructions above or below of what you want to happen and where you want it to happen and when this or that is scanned in this or that cell.
Refer to specific cells, rows, columns in the descriptions.

Howard

 

[EFANYoutube]

If you have multiple users changing things, my suggestion is to do this in Access as it is a forms based program and good with inventory. Also Access is able to do querys easier and faster.
I would like to help you.
It will take only a couple of weeks for a simple design.
Something like 1 form 3 tables and a few queries would do it

 

[EFANYoutube]

What do you u think?