RFID student attendance worksheet

yvautrin

New Member
Joined
Sep 11, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 15, 2013
Messages
1,049
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,776
Members
416,202
Latest member
donya ba

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
Top