Quote Originally Posted by Maudibe View Post
Hi Freak,

I write a lot of code for bar code scanning. A bar code scanner is the same as a card reader in that they are input devices that enter characters into a cell one character at a time. Unfortunately, there is no event for a cell as the characters are being entered, only after the selection moves to another cell is the change event fired. This is OK if you want to hit enter or click another cell after every swipe.

The better approach is to use a userform with a textbox that will receive the ID. A textbox has a KEYUP event that can count and analyze the characters as they are being input. The userform's modal property is set to false so that it can stay open while you perform other tasks if needed, such as erase an entry, and still monitor for a read from the card reader.

Here is how it works:
1. The code is set to look for IDs of a certain length, ex. all IDs have 5 characters bit this can easily be change to any length
2. Since the textbox retains the focus, all reads from the cardreader are dumped into the textbox one character at a time. Each character entered triggers the textbox's keyup event.
3. The textbox KeyUp event analyzes the length of the ID as each character is inputted and when that condition is met, the code performs an action.
4. The code compares the entered ID to count how many instances there are in column A. Base on what it finds, actions are performed based on the following scenarios

Scenario 1: No instances are found (Unique)- the ID is placed in col A and it is assumed that this therefor is a swipe in so the date and time are stamped in the in columns
Scenario 2: 1 or more instances are found- The code finds the last instance and determines if there was a swipe in and a swipe out. If there is a swipe in but no swipe out then the swipe out columns are stamped with a date and time. If there is both a swipe in and a swipe out then the code creates a new entry line placing the ID and a swipe in

The logic is fairly simple but manipulating the input of the characters to elicit an event with no user intervention is the trick.

Here is the code
In the user form module, place the following code:
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'------------------------------
'DECLARE AND SET VARIABLES
Dim Row As Long, LastRow As Long, NextRow As Long, x As Long
On Error Resume Next
If Len(TextBox1.Value) = 5 Then 'CHANGE TO THE LENGTH OF THE IDs
    With Worksheets("Sheet1")
        Row = 2
        LastRow = 2
        NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        x = WorksheetFunction.CountIf(Columns("A:A"), TextBox1)
'------------------------------
'NO PREVIOUS LOGIN
        If x = "" Then
            .Cells(NextRow, 1) = TextBox1
            .Cells(NextRow, 2) = Date
            .Cells(NextRow, 3) = Time
'------------------------------
'PREVIOUS LOGIN
        Else
            For I = 1 To x
                Row = WorksheetFunction.Match(TextBox1, .Range("A" & Row + 1 & ":A" & NextRow), 0) + LastRow
                LastRow = Row
            Next I
            Row = LastRow
'------------------------------
'PREVIOUS LOGIN- COMPLETED
            If .Cells(Row, 2) <> "" And .Cells(Row, 4) <> "" Then
                .Cells(NextRow, 1) = TextBox1
                .Cells(NextRow, 2) = Date
                .Cells(NextRow, 3) = Time
'------------------------------
'PREVIOUS LOGIN- NO OUT RECORDED
            Else
                .Cells(Row, 4) = Date
                .Cells(Row, 5) = Time
             End If
        End If
        TextBox1.Value = ""
    End With
End If
End Sub


Private Sub UserForm_Activate()
TextBox1 = ""
End Sub
Attachment 539489

Attachment 539491

Thank you so much for your assistance! The ID card reader does automatically enter itself. After any card is swiped, it records the data on a cell and automatically moves down(essentially pressing the enter key). As I have said before on other comments, I don't know any of Marco or Code related to excel at all. I also barely understand the functions (all the thing I have got was found online but I changed a little bit).

Here's the file I have (there might be some useless codes on E or F columns . Please let me know if i can clarify any thing that's there. Thank You again !
Attendace Log Trail.xlsx