Custom Barcode Inventory System Using Excel

[Nikkolas Camp]

So, I'm wanting to build a custom inventory spreadsheet to better track out material in house. We have a process to tracking our inventory, it being tracked in total square yards. We currently do our inventory the old fashion way, that being with our trusty pencil and paper, read all tags starting with identifying product, amount of product on one skid, and lastly the total square yards on pallet. We record that information and move on to the next product. We have a decent size warehouse, but even if it were far smaller I'm sure you can imagine how tedious this process is. So what I am capable of doing to creating barcodes containing all information in regards to the material.

Ex. Product ID, Lot No., Width, Length, Rolls per Pallet, Rolls per Shipment, Sq. Yds., PO No., Date Received, and lastly supplier.

I want to create a spreadsheet where all I've listed above is a column; and when I scan said barcode, excel inputs all this information in each cell corresponding to the information the scanner has read. Example below.

So in short, I want excel to be able to input all information provided by the barcode in the proper column once scanned. I hope this is enough information to help the community help me get to my goal.

Hope to hear from you all soon,
Nikkolas C.

 

[mikerickson]

Scan the barcodes into a column. Then use Text2Coulmns with fixed width to split that into the different fields.
I don't know if scanning a barcode will trigger the Worksheet_Change event, but if it does, you're golden.

 

[Nikkolas Camp]

Scan the barcodes into a column. Then use Text2Coulmns with fixed width to split that into the different fields.
I don't know if scanning a barcode will trigger the Worksheet_Change event, but if it does, you're golden.

Is there a way to format the table to where (Text2Coulmns) works as you enter data into a cell?

Ex. I enter data and have the Text2Coulmns formatted to detect commas and splits the data automatically?

 

[mikerickson]

THat would be a Change event, not a built in Excel feature.

 

[Sequoyah]

Hi Nikkolas,
here is an example using VBA, instead of inserting the scanned code into a column, this is inserted into the textbox of a Userform, then it's automatically split into its columns.
FileDropper

 

[Sequoyah]

Hello to all the forum and thanks to Mike for the appreciation. Today I could test with a barcode scanner and I realized that the Change event is being triggered after only the first character of the barcode is displayed in the textbox, so every single character is written on a new line.

I therefore made a small change to the code and attach the link to the updated file. FileDropper
Here's the code

for the Userform

Private Sub TextBox1_Change()
    Dim DoTime      As Date
    ' set time to 1 second in the future.
    DoTime = Now() + TimeValue("00:00:01")
    
    ' tell Excel to run the 'DoAction' sub routine in one second
    Application.OnTime DoTime, "DoAction"
    
End Sub

in a standard module

Sub DoAction()
    
Dim tbl             As ListObject
Dim LastRow         As Integer
Dim Rng             As Range
    
Set tbl = ActiveSheet.ListObjects("Table1")

LastRow = tbl.Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

tbl.DataBodyRange(LastRow, 1) = frmBarcode.TextBox1

Set Rng = tbl.DataBodyRange(LastRow, 1)
Rng.TextToColumns Destination:=Rng, DataType:=xlFixedWidth, _
                  FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(17, 1), Array(19, 1), Array(24, 1), _
                  Array(25, 1), Array(26, 1), Array(33, 1), Array(37, 3), Array(46, 1)), _
                  TrailingMinusNumbers:=True

frmBarcode.TextBox1 = ""

End Sub

 

[mikerickson]

@Sequoyah Did you try the first code in the AfterUpdate event rather than the Change event to prevent it running for each character?

 

[Sequoyah]

Hi Mike,

AfterUpdate event requires further action from the user, my goal is to automatically run the macro after scanning the code.

I'm open to any suggestion from the most experienced, we still wait for Nikkolas' opinion.

 

[Nikkolas Camp]

Good Day to both @mikerickson and @Sequoyah! I want to first thank you both for taking the time to help me with this. I have to be 100% with you, I'm learning as we go not only to finding the answer to my initial question, but with excel as a whole. I know the basics; but when we get to advanced coding and manipulation to said program, the remedy may have to be broken down for me to fully understand. E.g. the VBA code @Sequoyah provided above. Ill be looking into how to implement said coding you provided and only then will I be capable of giving my opinion on it. If you have any special instructions or recommendation on how to do this task, Id like to read it.

 

[Nikkolas Camp]

Ok, so I didn't realize you had left a file for me to try. Talk about me overcomplicating.

So with this macro, we are getting closer to the remedy to my initial question. Above is a screen grab of the results of what you have provided @Sequoyah. It took the information and split it as you can see, but it did so in a weird way. May I ask what tells the program how/where to split the information?

I've been researching and playing with this idea since my first post and I found that in Excels Text to Columns feature (as suggested by @mikerickson) you can have it split with a comma or a symbol of you choosing. I decided on a *asterisk, seeing how a comma would be problematic. Ill attach some screen grabs as we figure out the solution and give as much info as I can to further explain my findings so there's a full understanding on my end. For starters (though I doubt this matters) I played with both a QC and Code 128.

Both barcodes work the same I found. When scanned, the information output is the same (CV222Grn*123456789*60*3,900*1*7*2,166.7*9876*12-Sep-22*Berry*). As you can see, I split the information with asterisk which is what split the information perfectly. With the macro you provided @Sequoyah and this method of dividing the information, that (at least in my head) would solve the problem with the info in its completion in the correct columns.

Barcode (when scanned) inputs in this order

Product	Lot No	Wd	Lg	Rolls/Skid	Rolls/Ship	Sq Yds	PO No.	Received	Vendor
CV222Grn	123456789	60	3900	1	7	2166.7	9876	12-Sep-22	Berry