RFID student attendance worksheet

[yvautrin]

Hello World!

My VBA/Macro skills being very poor, I am lost facing the problem I have to solve!

I need to setup a student class attendance system, using a USB RFID reader, and I was thinking about Excel, I found some stuff on the web, but nothinf fulfilling my needs....

What I need :

Student swipe their cards when walking in the classroom, a timestamp is created in a column "punch in", if they swipe card again after a certain delay (let's say 5 minutes), another stamp is added in a new column "punch out".

In the excel file, a worksheet containing students names/class/RFID tag number where the code/macro will do a lookup to attach student name to rfid_ID.

Not sure my explanations are clear....but your help will be appreciated!

Thanks for your support!

 

[NoSparks]

Maybe something along the lines of this, although I can't test it as I'm not able to simulate whatever the eighth character is from the reader.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long, findString As String, Scan1 As Range, Scan2 As Range
   
If Target.Address <> "$A$2" Or Target.CountLarge <> 1 Then Exit Sub

On Error GoTo Eventsbackon
Application.EnableEvents = False

findString = Target.Value
With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    
    Set Scan2 = .Range("A3:A" & lr).Find(What:=findString, LookIn:=xlValues, LookAt:=xlWhole, _
             SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    'if found is already clocked-in
    If Not Scan2 Is Nothing Then
        If Now - TimeSerial(0, 2, 0) > CDate(.Cells(Scan2.Row, 4)) Then
            .Cells(Scan2.Row, 5) = Format(Now, "dd/mm/yyyy hh:mm:ss")
        End If
    'not found so must be clocking-in
    Else
        Set Scan1 = Sheets("Sheet2").Range("A:A").Find(What:=findString, LookIn:=xlValues, _
             LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not Scan1 Is Nothing Then
            .Cells(lr + 1, 1).Resize(, 3).Value = Scan1.Resize(, 3).Value
            .Cells(lr + 1, 4).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
        End If
    End If
    .Range("A2").ClearContents
    .Range("A2").Select
End With

Eventsbackon:
Application.EnableEvents = True

End Sub