Macro for Login attendance

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

You have 60 posts, you need to be using code tags.

Please go back to your first post, click edit, highlight all your code then click the # on the tool bar and choose save changes.

Originally Posted by srinivasan1965

Hi
Done
is it possible to modify the code while entering id it displays date and time also on the screen
Thanks

It doesn't look like it worked.

After putting code tags around the code and saving the changes you should see your code go from looking like this.

Sub Test()

End Sub

To this:

Please Login or Register  to view this content.