Track users login and logout time / day

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 2019
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:
 

makis1023

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 2019
Consider this solution.
Create a new worksheet ('Track Activity' or whatever) . Keep it hidden except when you view it.

Track Activity data:
UserActionDate/Time StampElapsed
User 1Sign In06/28/21 09:30 AM
User 1Sign Out06/28/21 01:00 PM3:30
User 2Sign In06/28/21 02:00 PM
User 2Sign Out06/28/21 03:34 PM1:34
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 2019

ADVERTISEMENT

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

VBA Code:
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

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jan 31, 2005
Messages
125
Office Version
  1. 365
  2. 2019
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

New Member
Joined
Jun 16, 2021
Messages
18
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,147,822
Messages
5,743,400
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top