• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in creating Login- Logout time tracking macro

Status
Not open for further replies.

Aswinraj

Member
Hello Can anybody please help me on this.
1. When User Enter the Employee ID in HOME Page, it should fetch the name from "Sheet 2".
2. When LOGIN Button is pressed - it should display the Time (Once Login is pressed - Log In Button should be disabled)
3. When LOGOUT Button is pressed - it should display the Time (Once LogOut is pressed - Log Out Button should be disabled)
4. YOUR TOTAL LOGIN HRS FOR TOTDAY IS - when LOGOUT is pressed, it should display total hours spend today.
(NOTE: Other than Employee ID Text Box, all other Items should be disabled for editing for user)

5.Once Log In is pressed - Current Date, Employee Name, Login Time sould be saved in Database sheet. (Should not be editable)
6.Once Log Out is pressed - it should display the time in Logout Column.(Should not be editable)
I had attached the excel file which needs these changes. Kindly update the coding to the attachment, which will be helpful.
 

Attachments

  • AMS Sheet.xlsm
    45 KB · Views: 280
@Aswinraj - for testing purpose ...
I didn't find #6 'Logout Column' ... this part needs more information
I add some functions for [ReSet]-button too.
Ideas ... Questions?
 

Attachments

  • AMS Sheet.xlsm
    45.7 KB · Views: 99
I Tested the file, Log In, Log Out disappear and Reset button is working fine.
But in YOUR TOTAL LOGIN HRS FOR TOTDAY IS i could see ### (I think the column needs to be extended) also in Database Sheet i could not see the date, it shows as ####.

Changes still needed as below,

1. Without Entering the Employee ID,User should not able to press Login.
2. I will be locking Sheet2 and Database, So Sheet 1 and Query Tracker tab should be visible only when User press LogIn.
3. LOGOUT - Can you please share your idea on this.
(There was a changes for user leaving Office earlier and after reaching home they can press LOGOUT, so that i will shows as they are in office but they cheated.) It should not be.., There must be some restrictions. Do you have any idea on how to do that?

My Idea in Logout restrictions was - When user choose options (Break, Meeting,Training,Fun Activities, permission ) from Category in sheet 1, then the system can be idle more than 2 hrs but when user chooses any other options otherthan above mentioned category then the system should not be idle more than 30 Minutes, if it is idle more than 30 minutes then Logout should be automatically enabled. (Not sure if it is possible, just an idea)
 
@Aswinraj
### - done!
1) before - it didn't accept unknown ID ... and so on
now - [Log In] will be visible after ID has entered.
2) You can make those settings; Home-tab have to visible all the time!
Hox! Sheet2's range[H1:K1] have to be unlocked as now!
3) Still same challenge!
Start Date/Time needs ONLY one column as well as End Date/Time!
Both of those columns could use for this purpose ...
if those columns updated automatic and
if User cannot edit those two columns ...
for example, everytime then Sheet1 will activate, all rows would check and if needed it could make 'system Logout' with some kind mark.
Ideas ... Questions?
 

Attachments

  • AMS Sheet.xlsm
    50.2 KB · Views: 163
This interesting and well done. Although not because here you have everything in access without password the macro, I believed that everyone you gave your support macro blocked
best regard
 
@vletm, Login, Logout, Rest is working fine. But need few more changes.

1. Database Sheet: User should be allowed to copy the data from Database sheet but they should not able Edit or modify it.

2. Login - When User clicks login it should automatically go to Sheet1.

3. Logout - I need some restrictions in this, In Sheet1 there is a Category Column. So in Sheet1 Category Column when user chooses any other options other than Break, Meeting,Training,Fun Activities, permission then the system should not be idle more than 30 Minutes, if it is idle more than 30 minutes then system Logout should be automatically enabled. Is it possible.
 
@Aswinraj
1) You wrote that You 'Database' isn't visible ... now You say that they should be allowed to cody from it ... hmmm ... anyway, now it's possible - but not with 'copy&paste'. There could be 'easier' way to let user to do this too..
2) Home -> Login -> Sheet1 --- You didn't tell before ... now works
3) So far there is no connection with 'Name' A-col and 'Name' G-col on sheet2!
If there would be some kind of connection, then the system logout could be possible. Of course those bolds have to be listed somewhere too.
>> Now, there is ( You have) a minor challenge with Log In / Out!
As You have had an idea, Log-times saves after Log Out.
If someone else tries to Log In ... have You think about that kind of case?
 

Attachments

  • AMS Sheet.xlsm
    50 KB · Views: 61
Last edited:
@vletm,

1. Database sheet is Visible, but user cannot able to copy paste the data. Since for Audit purpose we need to copy those data to another excel/system. So User should be allowed to copy/ those data.
2. Home - Login - Sheet1 is working but when we get back to Home sheet to LOGOUT, the Logout Button is not visible.
3. Name A-col and Name G-col are same only, just Name G-col consist of Employee ID.
4. As you said, i added those 5 bold items in sheet 2 (Column E), for Logout scenario which i said i reply 7.

>> When User clicks Login it should save the time in Database sheet, and when Logout then it should save the time in Logout Sheet.
 

Attachments

  • AMS Sheet (3).xlsm
    42.1 KB · Views: 46
Last edited:
@Aswinraj - for testing purpose
1) Sheet3 for that case
2) Gotta write ID again!
3) Sreepathi = Name, Jaganathan = Revathy, R ... connection Okay?
4) Where do user can use those E-col items?
Where is selecting moment, (needs like dd/mm/yyyy hh:mm:ss)?
5) Changed Log In/Out procedure ...
 

Attachments

  • AMS Sheet.xlsm
    51.4 KB · Views: 44
@vletm,
1. No need separate sheet 3 for copying the data present in the Database sheet.
User should directly allowed to copy the data from Database sheet.
2. It looks like an extra step, were your should enter their employee id for logout once again. Instead when User clicks Login in - Login Button should be invisible and Logout button should be visible.
When User click logout then Logout button should be invisible.
3. I made the names inoder, now sreepathi - sreepathi...., (Note: we might add/remove more names from this list, so it should be dynamically updated)
4. You said to add those Bold items which i mentioned earlier in excel sheet, so that i had added those bold items in sheet 2 E-Column to perform Logout scenario which i said i reply 7.
 
@Aswinraj
1) How do You prevent edit, delete & paste over ... those values in the Database sheet?
2) If ONLY one user uses this file then NO need write ID, but I have understood that at least four users will use this. Is there one or more users for this file?
3) If 'name' and 'name' are in same row, no problem. Easier if only one name.
4) I found those items. As I tried to ask, how/when/where do users selections will be done/saved? If user selects (where do selection?), where do selections comes? Where is users selections timestamp (dd/mm/yyyy hh:mm:ss)? If nowhere is not those values, it will be challenge to try to do 'automatic logout'!
 
@vletm,
1 We can protect or lock the columns, which will prevent user from editing but allows to copy the data. (As done i Sheet1)
2. Yes there will be more than 1 user using this sheet.
Requirement is: When user enter the id it should display the name and Login and Reset Button should be visible. When user clicks Login, it should open Sheet1 and Login Button should be disabled but Logout Button should be Enabled. When User clicks Logout Button then both Login and Logout should be disabled. When User clicks Reset button then the same process should occurs.
3. Why does this step needed?
4. User will be making those selection in Sheet1, Category column dropdown.
 
@Aswinraj
1) Okay, I tried to tell, user can do all of those ... You can do it as You want.
2) One more time: User1 log in >> Sheet1 -> User2 want to Log in ... gotta Reset 1st, after that Log in >> Sheet1 -> User3 want to Log In ... gotta Reset 1st -> User2 want to Log Out ... hmmm ... with Your vision, there is User3's Log In informations in Home-tab ... what to do? If User2 only press Log Out, then actually User2 Log's Out User3 not User2! That's why every time there have to write ID! (or ... it could select from dropdown ... or why do You need that ID?)
3) Database saves 'Employee Name', Okay?
Your idea is to make 'automatic Log Out'.
Someway it have to know 'Employee Name' [G] not 'Nickname'[A]!
4) Okay ... but
Is there also [E]-list? NO!
Is there timestamp as I wrote? NO!
If not both YES, it will be challenge!
>> Ideas ... Questions?
 
@vletm:
1. This macro excel sheet will be provided to each individual person, so as i said Requirement is: When user enter the id it should display the name and Login and Reset Button should be visible. When user clicks Login, it should open Sheet1 and Login Button should be disabled but Logout Button should be Enabled. When User clicks Logout Button then both Login and Logout should be disabled. When User clicks Reset button then the same process should occurs.
(Since we have separate person to collect all the excel sheet and consolidate their login and logout timings)
2. Yes my id is to have automatic Log Out - In Sheet1 there is a Category Column. So in Sheet1 Category Column when user chooses any other options other thanBreak, Meeting,Training,Fun Activities, permission then the system should not be idle more than 30 Minutes, if it is idle more than 30 minutes then system Logout should be automatically enabled. Is it possible.
3. I havent provided any nick names, names given in sheet 2 are their names.
4. I could not understand this point.
 
@Aswinraj
1) Your comment was about different thing! You can delete 'Sheet3'! You take care that no unwanted things happen there! It's Yours!
1+) Comment to Your (1). Do You use ONE or MORE this kind of files?
ONE means, everybody uses same file and MORE means everybody has own file.
I need ONLY one word for answer (ONE or MORE).
2) You didn't answer my question at all!
Maybe after previous answer (1+), I could do something, but not before that!
3) Maybe my used term 'Nickname' could be Your term 'FirstName', no matter!
So, You don't have any idea what do I try to ask?
There have to be connection with 'Sheet2' A-col ... F-col ... H-col!
You use 'Sheet1' A-col with 'Sheet2' A-col.
If no connection with 'Sheet1' A-col value to 'Database' B-col,
it is impossible to do 'automatic logout'!
4) If someway You can select 'Sheet1' A-col any name, it needs also timestamp (action time dd/mm/yyyy hh:mm:ss) if You want that something would happen ~30mins later! I've tried to tell many many times that there is still same 'mistake' ... I don't want to tell same thing every time!
>> Try to sit, breath, think and read all my replys. I need answers!
>> You should try to understand few basic points and I try to help.
 
1. I am not speaking about sheet 3 here and i havent mention sheet 3 anywhere in my previous reply. and i already mentioned "this excel is provided to each individual persons to track their timings and in your language its MORE
2. For your 2nd point - answer was already given in my reply 15 (1.)
i think you didnt get my point. See Each person will have this excel sheet on their own. So as per your initial modification which you provided for testing, the login system should be. also i explained in my replys above.
3. Yes Sheet1 A column connects with Sheet2 A column. Sheet 2 A column connects with sheet 2 G column.
Database sheet is nothing but the data of user who logs in and logs out thats it.
4. Logout 30 Minutes later - I am clear in my point with my previous replys on what basis it should be and where and how it should be.
Please try to understand my needs and do needful of my requirements.
If you still didnt get my point then ignore the Automatic Logout option. Kindly do needful for other issues.
 
@Aswinraj
1) MORE Okay
2) No comment
3) You'll make that connection
4) Log Out 30 min later if E-col items, gives msg
 

Attachments

  • AMS Sheet.xlsm
    58.7 KB · Views: 44
@vletm,
1. Login, Logout, Reset buttons are working fine as expected.
2. Auto Logout is still confusing and not as expected.

It should works as, In Sheet1 - when user chooses any option OTHER THAN Break, Meeting, Training, Fun_Activities, Permission from dropdown list in Category (Col B) and when the system or Excel sheet is idle for 30 minutes then it should be automatically Logout.

3. One issue, When i enter the employee id, its not picking the right person name.

upload_2016-1-20_14-18-30.png
 
Last edited:
Vletm,

1. I received an error as example " Sheet 1 Name is not matching with Sheet 2 Name" some thing like that.., So I just reorder the names in sheet 2 E column and Sheet 2 A column but after that, i receive an error as per the screen shot.
Can you please fix this.

Capture.JPG


2. When i logout the Employee ID, Login Time, Logout time, total hours disappears automatically. then what is the use of Reset Button? Can we keep as - When we logout the data displayed should be visible when user reset it,then it should disappear.

I believe after these changes, no more updates will taken place, i believe.

I had attached the AMS sheet copy, kindly check this and please fix the issues in this sheet.
 

Attachments

  • AMS Sheet (1).xlsm
    55.4 KB · Views: 26
@Aswinraj
1) That message is real! I cannot modify this if You're using 'whatever' values!
Try to find the reason below... read the message too!
Screen Shot 2016-01-20 at 13.39.43.png2) There're some good reason for that 'clearing', but I took it away ...
There are still something to modify, maybe You'll notice ...
 

Attachments

  • AMS Sheet.xlsm
    63.5 KB · Views: 138
@Aswinraj
Did You watch my screenshot at all?
1st check what 'Name' have You used in Sheet1 Cell[A2] it's NOT valid name anymore! I have done a lot of work with this! If You could remember like #10.
I've asked why You want to use 3 kind of 'names' (including ID).
Now, You suddenly changed Sheet2 A-column values, maybe better match with columns F&G, only You can know. But changing Sheet2's A-column don't change Sheet1 A-columns values! There can be many kind of combinations that cannot find in one second. Of course, I can take message away (there is still that challenge and I would check that message would give better clue for user).
Still, if Sheet1 A-column value cannot found from Sheet2 A-column and it cannot find connection with Columns F&G there is challenge (something wrong)!
If haven't test how do this work if Sheet2![A1] is empty ... this kind of (someone would say) 'minor' changes would make many kind of unwanted actions.
I can check this case a little later ...
 
Status
Not open for further replies.
Back
Top