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
 

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

MrExcel MVP
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
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

MrExcel MVP
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
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

MrExcel MVP
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,074
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

MrExcel MVP
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.
 

Forum statistics

Threads
1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top