How to create attendance automation using Excel VBA
Hi,

Currently, we are still using old fashion login attendance with manual sign in and sign out in print out template.
This wastes a lot of our time.Basically what i require from the excel macro that i can think of is as follows:-
1. when user clicks the excel file, a pop up message box requires him to login.
2. Once, logged in, user should be able to see master form.
3. master form should contain buttons for login, logout
4. once user clicks login or logout, date and time should record in user sheet as per time set in the user pc. date and time should record in different cell.
5. excel should not allow user to click logout prior click login. Error message “you have not logged in”.
6. i also need an administrator id to create user name, amendment of data and e.t.c.

thank you in advance.
Zuri Azwan

Excel VBA code for ThisWorkBook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
UserForm1.CommandButton2.Visible = True
UserForm1.CommandButton1.Visible = False
UserForm1.TextBox1.Visible = False
UserForm1.TextBox2.Visible = False
UserForm1.Label1.Visible = False
UserForm1.Label2.Visible = False
UserForm1.Show
End Sub

Private Sub Workbook_Open()
MsgBox “Please login. After finishing your work please click on the logout button.”
UserForm1.Show
End Sub

VBA code for the command buttons:
Function IsWorkBookOpen(FileName As String)

Dim FF As Integer, ErrNum As Integer

On Error Resume Next ‘ We turn off error checking
FF = FreeFile() ‘ The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF ‘we try to open the file and lock it
Close FF ‘ Close the file
ErrNum = Error ‘ capture the error number
On Error GoTo 0 ‘ Turn on error checking
‘Find which error happened
Select Case ErrNum
‘ No error
‘ File is not open
Case 0: IsWorkBookOpen = False
‘ Error for “Permission Denied.”
‘ File already opened by another user
Case 70: IsWorkBookOpen = True
‘ Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
End Select
End Function

Private Sub CommandButton1_Click()
Dim username As String
Dim password As String

username = TextBox1.Text
password = TextBox2.Text

‘checklogincredentials
‘If TextBox1.Text = “abc” And TextBox2.Text = “abc789” Then
‘MsgBox “You are logged in!”
Dim info
info = IsWorkBookOpen(“C:\familycomputerclub-website\username-password.xlsx”)
‘ we open the workbook if it is closed
If info = False Then
Workbooks.Open (“C:\familycomputerclub-website\username-password.xlsx”)
End If

Dim x As Integer
x = 2
Do While Cells(x, 1).Value <> “”
If Cells(x, 1).Value = username And Cells(x, 2).Value = password Then
MsgBox “Welcome!”
Sheet1.Range(“A1″).Value = Date & ” ” & Time
Selection.NumberFormat = “m/d/yyyy h:mm AM/PM”

UserForm1.Hide
ActiveWorkbook.Close True
End
Else
x = x + 1
End If

Loop
MsgBox “Please check your username or password!”
ActiveWorkbook.Close True
TextBox1.Text = “”
TextBox2.Text = “”
TextBox1.SetFocus

‘Else
‘MsgBox “Wrong username or password!”
‘TextBox1.Text = “”
‘TextBox2.Text = “”
‘TextBox1.SetFocus
‘End If
End Sub

Private Sub CommandButton2_Click()

Sheet1.Range(“B1″).Value = Date & ” ” & Time
Selection.NumberFormat = “m/d/yyyy h:mm AM/PM”

ThisWorkbook.Save

Worksheets(“Sheet1”).Range(“A1:B1”).Select

Selection.Cut

Unload Me

getlogindata

ActiveWorkbook.Close True
‘Application.Quit
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
Cancel = True
End If

End Sub

Visual Basic for Applications code for the relevant called macro or subroutine:

Function IsWorkBookOpen(FileName As String)

Dim FF As Integer, ErrNum As Integer

On Error Resume Next ‘ We turn off error checking
FF = FreeFile() ‘ The inbuilt function gets a free file number.
Open FileName For Input Lock Read As #FF ‘we try to open the file and lock it
Close FF ‘ Close the file
ErrNum = Error ‘ capture the error number
On Error GoTo 0 ‘ Turn on error checking
‘Find which error happened
Select Case ErrNum
‘ No error
‘ File is not open
Case 0: IsWorkBookOpen = False
‘ Error for “Permission Denied.”
‘ File already opened by another user
Case 70: IsWorkBookOpen = True
‘ Some other error occurred. Capture the error number for further action
Case Else: Error ErrNum
End Select
End Function

Sub getlogindata()

Dim info
info = IsWorkBookOpen(“C:\familycomputerclub-website\login-details.xlsx”)
‘ we open the workbook if it is closed
If info = False Then
Workbooks.Open (“C:\familycomputerclub-website\login-details.xlsx”)
End If
Worksheets(“Sheet1”).Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 2))

ActiveWorkbook.Save
ActiveWorkbook.Close

Application.Quit

End Sub
Watch the Excel VBA training video below for more details:

 

Further reading:
Get rid of the close button on a form

Download the sample file by clicking on the Excel icon. The complete VBA source code is given. Take care of your own file paths. Use ‘zuri’ and ‘azwan’ as user-name and password respectively.