Student Attendance

[vikas_newports]

Hello
I hope everyone is doing well, I have an issue while displaying the Currently Available Student in the Lab.

I want to Create a Dashboard of total students in a Lab with their "Name", "Total Time in Lab" and their "Arrival/Check-in Time" after they Check In. Once a student checks out, their information will drop out from the Dashboard.

Check the "Dasboard" sheet for and example of what a Dashboard would look like at two intervals: 1) 04:26 PM, and 2) 05:00 PM

Students will use RFID to check in. Ideally, I would prefer to have a message box or window of the Dashboard I described. This Dashboard will eventually get displayed On TV from a Computer in Lab for students to know for the time they have spent in Lab.

[vikas_newports]

Anyones here to helpme

[Richard Buttrey]

Hi,

Rather than getting VBA to add the relevant number of shapes and populate them with the data, perhaps this Pivot Table approach will help.

It's far easier than the code that will need to be written

The System time cell is currently manual entry for testing. In the real world you'd have that populated with the =NOW() function and then you'd need a macro which auto runs every n minutes/seconds and refreshes the PT.

[vikas_newports]

Thanks Richard , but Pivot table will show all the entries and I want to show only Present . and its presentation is not looking good that is why I avoided the Pivot table
any other idea for better presentation

[maniacb]

Here is a different approach

Sub checkUpdt()
Dim i, lr, k, f, g, v, x As Long
Dim sc, db As Worksheet

Set sc = Sheets("Students Checkin Time")
Set db = Sheets("Dashboard")
lr = sc.Cells(Rows.Count, 1).End(xlUp).Row
k = 0: f = 0: g = 0: v = 2: x = 2
With sc
For i = 2 To lr
    If .Cells(i, 1).Value = Date Then
        If Format(.Cells(i, 4).Value, "hh:mm") <= Format(Now(), "hh:mm") _
        And Format(.Cells(i, 5).Value, "hh:mm") > Format(Now(), "hh:mm") Then
            db.Cells(2 + v, 2 + g).Value = .Cells(i, 3).Value
            db.Cells(3 + v, 2 + g).Value = "Check in at:" & Format(.Cells(i, 4).Value, "hh:mm")
            db.Cells(4 + v, 2 + g).Value = "Total Time:" & DateDiff("n", .Cells(i, 4).Value, Format(Now(), "hh:mm"))
            k = k + 1
            f = f + 1
            g = g + 3
            If f Mod 3 = 0 Then
                v = v + 5
            End If
            If g Mod 9 = 0 Then
                g = 0
            End If
    
        End If
    End If
Next
End With
'MsgBox "updated"
MacroLoop
End Sub


Sub MacroLoop() 'this is the name of the macro that sets the OnTime
If Time > #4:41:00 PM# Then Exit Sub
Application.OnTime Now + TimeValue("00:01:00"), "checkUpdt"
End Sub

[vikas_newports]

Here is a different approach

Thanks Maniacb, but I not getting how it is working I changed the time of a student manually and tried to run macros but give error in Module 1 of Macro CloseL()
and the time is not updated. Might be I am doing something wrong . Will u please guide me how it is working

[maniacb]

The code has been updated to clear the Dashboard, that's why you weren't seeing updates.

Run StartSched to begin the Ontime process. It will run every minute. You can enter a cutoff time in this module for end of day.

To force turning off the Schedule update, run StopSched module. Let me know if you have any other questions

Dim TimeTo As Date

Sub checkUpdt()
Dim i, lr, lrd, k, f, g, v, x As Long
Dim sc, db As Worksheet

Set sc = Sheets("Students Checkin Time")
Set db = Sheets("Dashboard")
lrd = db.Cells(Rows.Count, 2).End(xlUp).Row
db.Range("B2:H" & lrd).ClearContents
lr = sc.Cells(Rows.Count, 1).End(xlUp).Row
k = 0: f = 0: g = 0: v = 2: x = 2
With sc
For i = 2 To lr
    If .Cells(i, 1).Value = Date Then
        If Format(.Cells(i, 4).Value, "hh:mm") <= Format(Now(), "hh:mm") _
        And Format(.Cells(i, 5).Value, "hh:mm") > Format(Now(), "hh:mm") Then
            db.Cells(2 + v, 2 + g).Value = .Cells(i, 3).Value
            db.Cells(3 + v, 2 + g).Value = "Check in at:" & Format(.Cells(i, 4).Value, "hh:mm")
            db.Cells(4 + v, 2 + g).Value = "Total Time:" & DateDiff("n", .Cells(i, 4).Value, Format(Now(), "hh:mm"))
            k = k + 1
            f = f + 1
            g = g + 3
            If f Mod 3 = 0 Then
                v = v + 5
            End If
            If g Mod 9 = 0 Then
                g = 0
            End If
    
        End If
    End If
Next
End With
'MsgBox "updated"
StartSched
End Sub

Sub StartSched() 'this is the name of the macro that starts the OnTime
If Time > #10:45:00 PM# Then Exit Sub 'Use this time to end code at certain time
TimeTo = Now + TimeValue("00:01:00")
Application.OnTime TimeTo, "checkUpdt"
End Sub

Sub StopSched() ' This Macro ends the Ontime schedule
    Application.OnTime TimeTo, "checkUpdt", , False
End Sub

[vikas_newports]

Thanks Maniacb, it is working fine , we have out time in the sheet, but what if we dont have out time of student I mean currently he is in the class then it must be shown as active student on screen currently this code is not showing him as present student in the lab

[maniacb]

Can you rephrase your question? Do you want a way to automatically checkout the student at a specific time?