Barcode Scanner CheckIn/CheckOut - Stumped

C

cval

New Member
Joined
Mar 29, 2012
Messages
18
Hello everyone!

I am trying to build an excel macro that records a date and time a package is scanned. I also want to record the time the package leaves.

I am using a barcode scanner to record the package tracking number and I found this handy code that timestamps the package when its scanned (Check In time)

However, I cannot figure out how to have scan the same package number and record a Check Out time on the same row.

So Column "A" is Package # Column "B" Check In time Column "C" Check Out time.

Code: 
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
 
On Error GoTo enditall
Application.EnableEvents = False
                    If Target.Cells.Column = 1 Then
                        With Target
                            If .Value <> "" Then
                            .Offset(0, 1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
                            End If
                        End With
                    End If
 
enditall:
Application.EnableEvents = True
End Sub

Any help would be greatly appreciated as I am just starting with VBA.

Thanks,
Christian
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Welcome to the Board!

I'd imagine that the scanner is only going to input data sequentially, but you should be able to build a pivot table off of it.

HTH,
 
Upvote 0
Hello Smitty thank you for the welcome.

Are you referring to the actual sequence of the items scanned? (I am scanning FedEx/UPS barcodes) or to the sequence in which they will be scanned in? (As a package is delivered it will be checked in and as it leaves it will be checked out).

What I am looking for is something that will update the date and time once the barcode is scanned for the first time (which the code I found does) and then once something is scanned again it will record the check out date and time.

Pretty much is there a way to incorporate a search for the barcode and then an IF statement that populates column C with a checkout time?

I'm not too familair with pivot tables so if you think they can accomplish this please let me know!

Thanks again
Christian
 
Upvote 0
Your code should record both check in and out times, building sequential records, which is good database functionality.

The problem with a formula is that it will be hard to take into account that you'll have multiple items come in before a particular item goes out, leaving a gap between scan id's. But a Pivot Table should be able to order all of that, giving you a summary by scan id of the In/Out times.

If you post an example of the scan data that you have, someone can come up with an example PT for you.
 
Upvote 0
The code only records the "Check in" time or the date/time when anything in column A is entered.

If a duplicate is entered it treats it as another row, what i want to accomplish is for the vba code or formula to catch this duplicate and record the date in a third column.

Unfortunately I do not have any data to show since i wanted to get this working before i started scanning bar codes.

I am trying to make it as simple as possible since non tech savy folks are looking to use it.

Again thanks for taking the time and suggesting a pt.
 
Upvote 0
If a duplicate is entered it treats it as another row
Click to expand...

As it should, the key is being able to identify it.

I'd start with scanning some examples and changing the column to Time In/Out and use the sequential entries to your advantage. Otherwise your change event will be running overtime trying to do sting comparisons/sorting that a Pivot Table could easily could do for you.

Have you thought about putting this into Access? For scan data, that's a better place then Excel, and they're modeled to talk to each other.
 
Upvote 0
I have not thought about putting it in access.

I had seen this functionality work previously on some demo data and I thought it would be easy to recreate. The folks who will be using want to keep it in excel - there is little concern over performance issues (when they do arise i will be sure to suggest the pivot table) but for the time being i will continue to explore string comparison.

If anyone has any insight on how to use the CompStr (can I add it to the change event or do i have to call it from a function) I would really appreciate it.

Thanks again for your time.
 
Upvote 0
You could tell your code to sort the data on entry, which would leave your tracking numbers in order.

Or if you absolutely have to have the In/Out times on the same row, then look at the Find Method in the VBA helpfile. There's a good example that you should be able to adapt. Essentially, when an item is scanned, you check for its existence. If it exists, then put the time out. If it doesn't exist, then add the Tracking # & Time In.
 
Upvote 0
Yes, I have been exploring the Find method, I am getting stuck at finding the "What" :confused:

Would it be:

HTML: 
If Target = Target.Cells.Find(What:= Target.value,After:=ActiveCell, LookIn:=xlValues, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)..Offset(0, 1).Value = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")

What would I call a cell value in the range?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Using barcode scanner to decrease quantity
Replies
1
Views
347
JoeL5
J
A
  • Question
Help with scanning barcodes into an excel invoice
2
Replies
12
Views
779
RoryA
RoryA
D
  • Question
VBA user form and barcode scannig problem
Replies
1
Views
252
bobsan42
bobsan42
K
  • Solved
Simple implementation question
Replies
5
Views
498
Katolux
K
S
  • Question
Scan Attendance
Replies
1
Views
320
Stewart11715
S

Forum statistics

Threads
1,216,474
Messages
6,130,841
Members
449,598
Latest member
sunny_ksy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top