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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
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?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 

jpburk239

New Member
Joined
Oct 5, 2010
Messages
4
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?
 

jpburk239

New Member
Joined
Oct 5, 2010
Messages
4

ADVERTISEMENT

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,
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

jpburk239

New Member
Joined
Oct 5, 2010
Messages
4

ADVERTISEMENT

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:)
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,135
Smitty code works for me.
you may need to reprogram you bar code reader to send an ENTER keystoke after it reads the data.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,604
Members
412,330
Latest member
carlosjw
Top