Live Time Scanning

jpburk239

New Member
Joined
Oct 5, 2010
Messages
4
Hi All,

I hope you can help me...

I run an events company that needs to track attendance at exact time that a person scans into an event.

So basically i need a 2 column spreadsheet, one with the person's bar code in column A, and as they scan in column a, it gives me the exact time they scanned in, in column B.

Thank you
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the board!

I may not be the one who is able to help you with this one, but I am guessing whoever can will need to know: how is the barcode getting into Excel? And when you scan it in, does it automatically skip to the next cell? Or is the cursor then just sitting there in the cell, waiting for someone to press enter?
 
Upvote 0
Welcome to the Board!

You can see if the barcode input will trigger a change event. If it will then it's easy to capture the cell that received the input and use the change event to enter the time stamp in the appropriate cell. If Excel doesn't recognize the entry (the last barcode project I dealt with didn't) you can use a calculate event to capture it.

See if this does what you want:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("A:A")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            Target.Offset(, 1) = Format(Now, "hh:mm AM/PM")
End Sub

If it doesn't we can try a calculate event.

HTH,
 
Upvote 0
Hi There

Thanks for your response... I will scan the bar-code into Column A with a hand held scanner from the bar-code on the name tag. Then all i need is the exact time to show in column b...

Any help or suggestions would be appreciated.

:)

Welcome to the board!

I may not be the one who is able to help you with this one, but I am guessing whoever can will need to know: how is the barcode getting into Excel? And when you scan it in, does it automatically skip to the next cell? Or is the cursor then just sitting there in the cell, waiting for someone to press enter?
 
Upvote 0
Thanks Smitty...

I must be honest, i'm not that advanced on excel. Can you try explain in a little more detail.

Thank you for your help, much appreciated.

:)

Welcome to the Board!

You can see if the barcode input will trigger a change event. If it will then it's easy to capture the cell that received the input and use the change event to enter the time stamp in the appropriate cell. If Excel doesn't recognize the entry (the last barcode project I dealt with didn't) you can use a calculate event to capture it.

See if this does what you want:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("A:A")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met (do your thing here...)
            Target.Offset(, 1) = Format(Now, "hh:mm AM/PM")
End Sub
If it doesn't we can try a calculate event.

HTH,
 
Upvote 0
That code goes in the worksheet specific module and assumes you'll be making changes in Column A. To try it, right click the sheet tab and select View Code, then paste the code in the new window that opens on the right. ALT+Q will exit you back to Excel.

Enter a value (anything) in column A and see what happens. Then try adding a barcode and see if the date updates. If it doesn't then it's time to try the calculate event.
 
Upvote 0
Is there any chance you can explain how the code works or where i must enter the code, or how it is done? I have been trying now for a while and cant seem to get it right.

Thanks Smitty:)
 
Upvote 0
The most common mistake is to put the code in a general module - it needs to go in the worksheet specific module (not any old sheet, but specifically the worksheet that you want the code to work in).

Entering anything (alpha or numeric) in column A will trigger the code.

What the code says is essentially this:

Look for changes in column A. If a change occurs then trap the cell that changed (Target) and move one column to the right (Offset(,1)) then enter the date. Locking the code to A:A will prevent it from seeing changes anywhere outside of that range.

You can PM me your e-mail address and I'll send you an example, just note I'm running out for the better part of the afternoon.
 
Upvote 0
Smitty code works for me.
you may need to reprogram you bar code reader to send an ENTER keystoke after it reads the data.
 
Upvote 0
you may need to reprogram you bar code reader to send an ENTER keystoke after it reads the data.

Thanks rp. I should have mentioned that a barcode scanner is essentially just a keyboard emulator that tricks your computer into thinking someone's entering keys on the keyboard. But it remains to be seen if yours will trigger the change event.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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