Create a Simple Attendance System in Excel with Barcode Scanning Capability

Existing System

The existing system is a pretty simple Excel tracking sheet.

As you can see, it was done with a simple sheet, in which Member's details and Attendance are tracked together.

Some formulas have been placed to get the total count of attendance in different dimensions, such as attendance per individual and per gathering, which will be explained at a later stage.

A unique identifier (Barcode) would be needed for each member. It depends on what approaches we are using, it can be as simple as entering the NRIC of the members or using some unique key (for example, we can generate one by using an Excel formula and referring to other member details, with an auto-running number) for this purpose.

As you might have observed, when a member has attended a cross mark ( "X" ) will be entered in that particular gathering.

So, the following is my intention to somehow automate the process.

My Planning

I have in mind what is needed:

1 ) Buy a barcode scanner

2 ) Select a suitable barcode font to install

3 ) Develop a macro that will be able to receive barcode text

4 ) Put in the formula

5 ) Testing

1 ) Buy a barcode scanner

A standard barcode scanner will do the task.

2 ) Select a barcode font to be installed

A suitable barcode font is needed to make things easier, so there will be no need to use any extra library of components to generate the barcodes.

There are plenty of free barcodes fonts available online.

In this case, I have selected a barcode font from IDAutomation.

To download it, please refer to the following link:

• Barcode Fonts by IDAutomation®

Once you have gotten the barcode font, simply right click the downloaded font file and click Install to install it.

3 ) Develop a macro that able to receive the barcode text

We have now entered into the final phase before we will be ready for testing.

To start, we will need to create a userform, so that it can be used to accept the needed inputs, either by manually keying them in or via the scanning of a barcode.

a ) First, launch the Visual Basic Editor in Excel. 

To do this, go to the Developer tab and click Visual Basic, or simply press the shortcut keys of Alt + F11.

b ) Right-click any of the files in the Project Explorer and add a UserForm. See below;

c ) There are a few controls we are able to put into the form.

d ) To load the Event Date

We can simplify things so that the event dates are entered and tracked in this same worksheet, as long as the event dates are in a sequential alignment in columns.

To get the macro codes to load the event dates into the dropdown list in the userform would be done as follows:

    Dim LastCol As Integer, idx As Integer
    Dim EventDate As Variant
    Dim EventName As String
    Dim SelectedIndex As Integer
    SelectedIndex = 0
    
    LastCol = Sheets(ws_Member).Cells(3, Sheets(ws_Member).Columns.Count).End(xlToLeft).Column
    If LastCol < Sheets(ws_Member).Range(cell_FirstEvent).Column Then
        MsgBox "No Events in Sheet Member", vbCritical, "Error"
        Exit Sub
    End If
    For i = Sheets(ws_Member).Range(cell_FirstEvent).Column To LastCol
        EventDate = Sheets(ws_Member).Cells(Sheets(ws_Member).Range(cell_FirstEvent).Row, i)
        EventName = Trim(EventDate)
        If EventName <> "" Then
            If IsDate(EventDate) Then
                cmbEventDate.AddItem Format(EventDate, "d-MMM")
                If EventDate <= Date Then
                    SelectedIndex = idx
                End If
            End If
            idx = idx + 1
        End If
    Next
    cmbEventDate.ListIndex = SelectedIndex

e ) To add/remove attendance

We can use the following code to make this happen:

Private Sub txtBarcode_Enter()
    SendKeys "{Home}+{End}"
End Sub

Private Sub txtBarcode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        Call cmdOk_Click
        KeyCode = 0
    End If
End Sub

Private Sub txtBarcode_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    SendKeys "{Home}+{End}"
End Sub

Private Sub cmdOk_Click()
    If IsValid() Then
        Set rng = Sheets(ws_Member).Range("J:J").Find(txtBarcode.Text, , xlValues, xlWhole)
       
        If Not rng Is Nothing Then
            If optAdd.Value = True Then
                Sheets(ws_Member).Cells(rng.Row, eventIdx) = "X"
                msg = "Marked Attendance for : " & Sheets(ws_Member).Cells(rng.Row, "B") & " (" & Sheets(ws_Member).Cells(rng.Row, "C") & ")"
                Call SetColor(lblMsg, msg, vbGreen)
            Else
                Sheets(ws_Member).Cells(rng.Row, eventIdx) = ""
                msg = "Removed Attendance for : " & Sheets(ws_Member).Cells(rng.Row, "B") & " (" & Sheets(ws_Member).Cells(rng.Row, "C") & ")"
                Call SetColor(lblMsg, msg, vbCyan)
            End If
        Else
            msg = "Member ID " & txtBarcode.Text & " could not be found."
            Call SetColor(lblMsg, msg, vbYellow)
            txtBarcode.SetFocus
            SendKeys "{Home}+{End}"
            Exit Sub
        End If
    End If
    txtBarcode.SetFocus
    SendKeys "{Home}+{End}"
End Sub

Private Function IsValid() As Boolean
    lblMsg.Caption = ""
    If cmbEventDate.ListIndex = -1 Then
        msg = "Please select Event Date"
        Call SetColor(lblMsg, msg, vbYellow)
        cmbEventDate.SetFocus
        IsValid = False
        Exit Function
    End If
   
    Set rng = Sheets(ws_Member).Range("AA3:OZ3").Find(cmbEventDate.Value, , xlValues, xlWhole)
    If rng Is Nothing Then
        msg = "Event Date " & cmbEventDate.Value & " could not be found."
        Call SetColor(lblMsg, msg, vbYellow)
        cmbEventDate.SetFocus
        IsValid = False
        Exit Function
    End If
    eventIdx = rng.Column
   
    txtBarcode.Text = Replace(Trim(txtBarcode.Text), vbTab, "")
    If txtBarcode.Text = "" Then
        msg = "Please enter the Barcode"
        Call SetColor(lblMsg, msg, vbYellow)
        txtBarcode.SetFocus
        SendKeys "{Home}+{End}"
        IsValid = False
        Exit Function
    End If
    IsValid = True
End Function

Private Sub SetColor(ByRef lbl As MSForms.Label, ByVal msg As String, ByVal color As Long)
    lbl.Caption = msg
    lbl.BackColor = color
    If color = vbYellow Then Beep
End Sub

In the example above, column J will be used to store the unique identifier (Barcode) for member comparison.

After that, we will need to create a button in the attendance worksheet, so that we can write a simple macro to launch the userform we created, like so:

Dim frm As frmMain
Set frm = New frmMain
frm.Show vbModal

4 ) Put in the formula



The Formula for 1) Total Attendance per gathering:

=COUNTA(AA4:AA500)

The end range row is changeable. This would define the maximum no of members per gathering.

For this example, it is set to a limit of the 500th row for the calculation.

The Formula for 2) Total Attendance per gathering:

=COUNTA(AA4:CO4)

The end range row is changeable.  This would define the maximum no of gatherings available.

For this example, it set a limit to col: CO for the calculation.

Finally, we now need to save the file as an Excel Macro-Enabled Workbook (.xlsm)

5 ) Testing

a ) Valid Entry - Add attendance

b ) Valid Entry - Remove attendance



c ) Invalid Entry

 


Ed! To learn more about the author. please visit his  Experts Exchange Profile Page.