A simple parcel tracker

[Boboka]

Hi, I am looking for a simple parcel barcode tracker but I do not have any idea with VBA code. I hope someone here could help me.

I am planing to have a sheet 1(parcels) where I scan in barcode in column A2, with their date and time in B2. It will then move next to A3 column pending for next scan etc..

For the collection of parcel, in sheet 2(collection), scan the barcode of the person if he have it, or key in the barcode in a search box. If the scanned barcode is found in sheet 1, we will scan the person ID barcode who collected it. The found parcel barcode will be deleted from sheet 1(parcels) row, but appeared in sheet 3(collected) with the parcel barcode, date and time the parcel arrived, date and time the parcel collected and the ID of the person collected it. If no parcel is found, just show “no parcel entry”.

Thanks in advance if anyone can help.

 

[Legacy 143009]

What I understand, you have something like this:

What you want is:

Am I correct?

 

[Boboka]

Yes. You are correct.

 

[Boboka]

Yes, the sheet 1 (like a warehouse) you shown is whatever parcel came we just acknowledge and scan it into excel with date stamp.

The sheet2 you have shown is to find a particular parcel by barcode when the person come to me, if it is found it can be collected by the person. If it is not found then “parcel not found “.

The sheet3 is the data collected you shown, yes exactly this way. Whatever parcel barcode collected, it should not appeared in sheet1 anymore.

Thank you for your time.

 

[Legacy 143009]

The sheet2 you have shown is to find a particular parcel by barcode when the person come to me, if it is found it can be collected by the person. If it is not found then “parcel not found “.

I have a question about this step:
Is there goning to be a searchbox to search for parcel barcode? Then if it is found, it will prompt Personel ID. You will input it also manually and this will be a new record in sheet2.

Something like this?

Right after the code will collect to data in sheet3, it will delete the parcel in sheet1.

 

[Boboka]

Yes, sheet 2 main purpose will be like a search box. We scanned/input the parcel Barcode in the box.

if parcel barcode is found in sheet 1( which is like a warehouse), it can be collected by the person. We scan/input the person id barcode.

This found parcel will be remove from sheet 1 after collecting. Parcel barcode, arrival date, parcel collected, person ID will appeared in sheet 3.

Thank you.

 

[Legacy 143009]

First input a Personel ID to Sheet2 A2. Then input a Barcode to Sheet 2 B2:

Sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

I hope this would be a good start for you.

 

[Boboka]

Hi, thank you so much for your time.
But for sheet 1, I find that for every parcel code I entered or scanned, it never auto show the arrival date?

Is it my error?

 

[Legacy 143009]

Oh sorry, I've totally missed that part. I thought the date information is coming from the scanner automatically. Find the sample:

Sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

In Sheet 1 macro, you may also have a validation check if barcode is in the correct format or not. Let's say each barcode must be 7 characters long. Then the code will be:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    If Len(Target.Value) = 7 Then
      Target.Offset(, 1).Value = CDate(Format(Now, "dd.mm.yyyy"))
    Else
      Target.Value = ""
      MsgBox "Invalid barcode!", vbCritical, "ERROR"
    End If
    Application.EnableEvents = True
  End If
End Sub

 

[Boboka]

Hi, thank you.

By using the new sample files, I do have a run time error.

I do have any question is that for sheet 2, it act like a searchbox. So after scanning Person ID, and Parcel Code if ParcelCode is found, a msg box "Parcel Collected". Not sure whether I do it correct by adding the code above.

 If exist Then
        MsgBox "Parcel Collected!", vbExclamation, "SUCCESS"
      End If
      
      If Not exist Then
        MsgBox "Barcode not found!", vbExclamation, "WARNING"
      End If

After each scan, the cursor will go back to A2 cell waiting for next scan instead of the cursor dropping to A3 cell.

Once again, thank you for your time.