Macro required for Attendance tracker - Login and Logout

[nirmalreigns]

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

 

[nirmalreigns]

Anybody? Please help me!

 

[nirmalreigns]

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