Macro required for Attendance tracker - Login and Logout

nirmalreigns

New Member
Joined
Jun 16, 2015
Messages
3
Hi,

I have a team of 50 people and counting. I would like to create an automated attendance tracker wherein a user should be able to login / logout with command buttons. As soon as he opens the file, he should be a pop up message requiring him to login. The logoff button should be hidden until the user logins. Once the user logins or logout, date and time (from PC) should be fetched in the excel sheet as well as in the other workbook (which will have data of n number of users and will be administered by one with a password)

Also when the user is not logged in then the same be marked as 'Absent' in the collated workbook. Since I'm new to macros, please also specify as in which excel file and where to paste the macro code. And also please provide me with the steps to do the same. Also let me know if multiple users can access the login file at a same time.

Please help. Its urgent!

Thanks

Regards,

Reigns


 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

nirmalreigns

New Member
Joined
Jun 16, 2015
Messages
3
Hello All,

Is there anyone who can help me fix this macro?

Private Sub Workbook_Open()
Dim myEMPID As String
Dim logWB As Workbook
Dim logWS As Worksheet
Dim logNextRow As Long
'
'this must hold the full path and filename to the workbook you will
'record the employee ID and login time into
Const LogWorkbookPath = "D:\Users\Username\LogBook.xlsx"
'this must provide the name of the sheet in LogBook.xlsx to
'put the employee ID into
Const logSheetName = "User Log"
'this must be the column to put the employee ID into
Const logEmpIDColumn = "A"
'this must provide the column to put the logIN time into
Const logInTimeColumn = "B"

'force the user to provide an input before continuing
Do While myEMPID = ""
myEMPID = InputBox("Enter your Employee ID to Log In", "Log In", "")
Loop
'prepare to open the log workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'open the log workbook
Workbooks.Open LogWorkbookPath
Set logWB = ActiveWorkbook
Set logWS = logWB.Worksheets(logSheetName)
logNextRow = logWS.Range(logEmpIDColumn & Rows.Count).End(xlUp).Row + 1
logWS.Range(logEmpIDColumn & logNextRow) = myEMPID ' enter EMPID
logWS.Range(logInTimeColumn & logNextRow) = Now() ' enter login date/time
logWB.Close True ' close and save changes
Set logWS = Nothing
Set logWB = Nothing
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myEMPID As String
Dim logWB As Workbook
Dim logWS As Worksheet
Dim logLastRow As Long
'
'this must hold the full path and filename to the workbook you will
'record the employee ID and logout time into
Const LogWorkbookPath = "D:\Users\Username\LogBook.xlsx"
'this must provide the name of the sheet in LogBook.xlsx to
'put the employee ID into
Const logSheetName = "User Log"
'this must be the column to put the employee ID into
Const logEmpIDColumn = "A"
'this must provide the column to put the logOUT time into
Const logOutTimeColumn = "C"
'
'loop until the user provdes an input
Do While myEMPID = ""
myEMPID = InputBox("Enter your Employee ID to Log Out", "Log Out", "")
Loop
'prepare to open the log workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'open the log workbook
Workbooks.Open LogWorkbookPath
Set logWB = ActiveWorkbook
Set logWS = logWB.Worksheets(logSheetName)
'we are assuming that the last entry on the sheet was
'made by YOU logging in earlier, not someone else.
logLastRow = logWS.Range(logEmpIDColumn & Rows.Count).End(xlUp).Row
logWS.Range(logEmpIDColumn & logLastRow) = myEMPID ' enter EMPID
logWS.Range(logOutTimeColumn & logLastRow) = Now() ' enter login date/time
logWB.Close True ' close and save changes
Set logWS = Nothing
Set logWB = Nothing
End Sub


Its is simple. The macro workbook will have userforms Login and Log out. When the user enters its employee id - that should match with the employee ID list in 'LogBook' workbook in 'User Log' sheet; if not matching then should get an error message. Eg: If the logbook has the emploee id list column. And it has the following number 207309, then when the user enters the same on to the Macro enabled workbook then the user can login or else if wrongly entered then he should get an error message. Also after succussfully entering the correct employee ID then the PC time and date should be captured and saved it into the LogBook - User Log sheet against the respective employee id.

Please guys. Please reply to my post.

Thanks,

Regards,

nirmalreigns
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,803
Latest member
Jeff abby

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top