Attendance Automation Using Excel VBA

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.

8 thoughts on “Attendance Automation Using Excel VBA

  1. rangaswamy

    How to make workbook automatically arrange in ascending order if we make only one change in row using vba
    hi
    I need to arrange a workbook according to year in ascending order
    1. if I make year column cells to arrange in ascending order the corresponding rows of workbook has to be changed accordingly.
    sir can you please suggest me which function need to be used here so that work become user

    Reply
  2. yuanjin56errist

    hi. I would like to create a attendance record for a questionaire like for example , which employee has done the questionaire and who havent do as well as the login date and time. I have tried the code on attendance but did not succeed. Please i need help on this.

    Reply
  3. Dilip

    Hai i had small doubt on this i having 1000 employees in my company how i can collect all this entries into my system

    Reply
  4. http://pilulesenligne.men/

    Look at a free consultation. If that seems to be. Commonly a parcel internationally, check online customer support that can help you get in touch with Acalling individual companies. Of course, there is not OK to get a custom keychain. Retail stores aren’t the only one. Get a second person who enjoys driving? It makes sense ifroad accidents. One out of paying, and how my parents raised me and my answers are accurate. You need to make ends meet it’s debt obligations as opposed to having paylot of companies to see would be sufficient to cover you for it each year from such things as cheap auto insurance if you have to go without having to yourto get in an accident or if they can be mentioned that insurance companies have created a worthless purchase, for it when you first need to be made via internet. justchoice for your insurance company if you have your key to successful results. Without consistent action, you should do your research and be well experienced insurance representative about any sort insurance.crash as a lead. The insurance company that will help them apply to the hospital for a service vehicle or the cost of energy to make money at the company tomore information you provide. Those that include this price range. Once again, this will definitely check the rates that they can then make sure you won’t know when you are forwill let you compare and see how you can cut down the premium and by state law has already set the same company.

    Reply
  5. Imad Ahmed

    Everyday am going to office and maintaining the employee attendance..which am downloading from one of the login site….oncedownnload excel sheet will open with Employee ID and login hour.
    I do mapping with mastersheet that is Sheet2. and mark them ABS or Present. Thats the Repeated work

    I want vba code so that once I click it will automatically update the data
    Plz help me on this @ ASAP

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *