+ Reply to Thread
Results 1 to 3 of 3

Macro required for Attendance tracker - Login and Logout

  1. #1
    Registered User
    Join Date
    11-10-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    13

    Exclamation Macro required for Attendance tracker - Login and Logout

    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.

    I have updated with the code found from the net but confused as to where to put the same.

    Please help. Its urgent!

    Thanks

    Regards,

    Reigns
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-10-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macro required for Attendance tracker - Login and Logout

    Anybody? Please reply... help needed asap!!!

  3. #3
    Registered User
    Join Date
    11-10-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Macro required for Attendance tracker - Login and Logout

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 02-17-2014, 07:44 AM
  2. login attendance macro
    By ringgo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2013, 12:06 PM
  3. login and logout time , with total login time ,break> 1 hours
    By shrinivasmj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-12-2013, 05:37 AM
  4. [SOLVED] Duration: login logout macro
    By city in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-11-2012, 11:25 AM
  5. [SOLVED] First login & last Logout
    By Vikky in forum Excel General
    Replies: 2
    Last Post: 08-19-2006, 01:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1