Track users login and logout time / day

[makis1023]

Hi all,

I need your help please.
I need users to enter username and password in order to open and edit an excel file and to keep login and logout time.
Using an extra excel file or worksheet in order to collect usernames and passwords is ok, as I just need that to have knowledge.
I know that this can be done with VBA but I am not vary familiar with VBA , I only know to do dummy things with VBA.
I don't upload excel file or code as this script will be used to an ordinary excel file.
Can you help me please?

Thank you all!

 

[RayFrye]

Do you really need to protect the Workbook (User name/password) or do you just need to track who/when the file opens and who/when the file is closed?

This might be alternative option:

Use the Track Changes Feature in Excel to Collaborate on a Workbook

Use the legacy Track Changes features in Excel instead of co-authoring to view changes made to a workbook when your team uses older versions of Excel.

www.lifewire.com

 

[makis1023]

Excel will be used by 3 different users.
All of them are working from the same computer. All users will have access to all data of the excel, I just need to track when a users logged in to work with excel and logged out, so password protected excel not for security reasons, but to just be able to track who had worked with excel and for how long.
I cant use sheet protection, just a basic username track system.

Thank you

 

[RayFrye]

Consider this solution.
Create a new worksheet ('Track Activity' or whatever) . Keep it hidden except when you view it.

Track Activity data:

User	Action	Date/Time Stamp	Elapsed
User 1	Sign In	06/28/21 09:30 AM
User 1	Sign Out	06/28/21 01:00 PM	3:30
User 2	Sign In	06/28/21 02:00 PM
User 2	Sign Out	06/28/21 03:34 PM	1:34

 

[RayFrye]

Put this code in "ThisWorkBook", assumes there is a Worksheet Named "Track Activity".

Option Explicit
Private LastRow, wb As Workbook, ws As Worksheet

Private Sub Workbook_Open() 'Fire when Workbook opened
    
    Application.ScreenUpdating = False
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Track Activity")
    
    ws.Visible = True  'Unhide Worksheet
    ws.Select          'Select Worksheet
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1   'Find the last row, then bump by 1
    
    ws.Range("A" & LastRow).Value = Environ("username")     'PC User
    ws.Range("B" & LastRow).Value = "Sign IN"               'Action
    ws.Range("C" & LastRow).Value = Format(Now(), "mm/dd/yy h:mm:ss")    'Date/Time Stamp and Format
    
    ws.Visible = False   'Hide the worksheet
    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Fire when user closes Workbook

    Application.ScreenUpdating = False
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Track Activity")
    ws.Visible = True  'Unhide Worksheet
    ws.Select          'Select Worksheet
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1    'Find the last row, then bump by 1
    
    ws.Range("A" & LastRow).Value = Environ("username") 'PC User Name
    ws.Range("B" & LastRow).Value = "Sign OUT" 'Action
    ws.Range("C" & LastRow).Value = Format(Now(), "mm/dd/yy h:mm:ss")   'Date/Time Stamp and Format
    ws.Range("D" & LastRow).Formula = "=TEXT(C" & LastRow & "-C" & LastRow - 1 & _
    "," & Chr(34) & "h:mm:ss" & Chr(34) & ")"
        
    ws.Visible = False   'Hide the worksheet

End Sub

 

[makis1023]

Thank you for your effor and code.

This working perfectly but I need to enter username and password, 3 users will work from the same pc/

 

[RayFrye]

All users, multiple users, on a single PC run from the same Windows User? I never considered this as an option in todays world.
Consider creating separate users for each employee.
As this is a volunteer organization, someone else might pick up and run with your problem.
I did what I thought was a solution.
Take care and good luck.

 

[makis1023]

Yes, I understand and thank you. Not seperate users at the same time. Consider that this excel will be for a cafe-bar so empoloyees need to enter cashier data, not at the same time. So need to log when they do the changes. To loggin with their password.

Anyway, really thank you for the try!