Need help in creating Login- Logout time tracking macro

[Aswinraj]

@vletm, Still you are not taking my point at all.

Coming to your point again, anyhow if the names are same then it should work right but in this case its not.

I checked your screen shot and it shows that name is invalid, so that i made all the names as similar one. Even before i changed the names, still it throws the error in the sheet when i choose the dropdown "invalid name missmatch"

For your question WHY U WANT TO USE 3 KIND OF NAMES -
Understand that Sheet2 is for fetching the NAMES and CATEGORY in Sheet1.
So that Names are present in 2 places in Sheet2 not in 3 places.

As i said before all the names included in Sheet1 A, Sheet2 A, G are the same.
I can remove the name column which i provided in Sheet2 H column and Employee id from G column. and i can keep the Sheet 2 as below, But when i make these changes it will affect your codings and validation which you done in Sheet1 before. So by considering your work i just placed and same name to H column in Sheet2 and provided the Employee ID. Hope you understand now.

 

[vletm]

@Aswinraj
I changed some settings with checking Names and so.
I made that code with those setting as well as possible.
There can be/will be something still, but I cannot test all possibilities.
There should be one kind of logic; what can do or so ...
>> About Your latest 'screenshot',
that would make too much work to change EVERYTHING again ... again.
>>Now, You have 'three times same information' (my term) like:
Sheet2's A-column has 'names' for Sheet1's A-column
Sheet2's F&G-columns has information's for Log In/Out (+few checks)
Sheet2's Range[H1:K1] is in that place, with its use!
>> If You want to change some of those, You can do whatever, but You will take care affects too - too much work.
>>>> Test this version...

 

[Aswinraj]

@vletm, sure i will check this version and let me know.., and yes i know that i will affects too also too much of work.

NOTE: i just provided that screen shot changes and said "considering your work i just placed and same name to H column in Sheet2 and provided the Employee ID. Hope you understand now." i havent asked you to make the changes as per the screenshot.

 

[Aswinraj]

@vletm,

One Issue i noticed as of now is with Login, when i enter the employee id, it shows the name but not Login Option.

 

[vletm]

@Aswinraj
That employee have to Log Out before can Log in, Of Course!

 

[Aswinraj]

@vletm,

Just need a clarification, Auto Logout will not happens when User chooses options as BREAK,MEETING,TRAINING, FUN ACTIVITIES AND PERMISSIONS right? If i need to add one more option as DAILY TASK REPORTS in this list for avoiding AutoLogout, in which place should i need to update or do modification?

 

[Aswinraj]

@vletm, inorder to finalize the sheet, i made the alignments as per the attachment. I Just Locked Home and Database with the Password Aswin-02 and I Hided Sheet2 and Sheet 3 with Sheetsettings in VB. And Hided I,J,K Columns in Sheet2. Provided the Names for Columns in A,B and C in sheet2.
After these changes it was working fine for some hours and now i am receiving an error when i click "Login" it was not moving to sheet1 and it throws an error as "400 Error" also Login,Logout is not saving in Database sheet.

VB password: Iwillnottellyou

I had attached the sheet for your reference. Kindly look into this.

 

[vletm]

@Aswinraj
I have been worried about those.
I tried to ask many times, how to select those 'new five' ... without answer.
Then I found my solution, because those 'new' (BREAK,MEETING,TRAINING, FUN ACTIVITIES AND PERMISSIONS) were in another column.
ALL NORMAL column selections will make possible 'Auto Logout'.
You DIDN'T add those to normal selection.
You could try to add 'second part' in every 'data validation' (normal & those extras). If You add now for some reason those to 'normal column' and You want that those don't make possible to 'Auto LogOut' the result will be much slower.
This isn't good ... for user.
This 'last minute extra change' needs extra coding to avoid Auto LogOut.
'The place' is where checking is something on 'normal' list.

 

[Aswinraj]

@vletm, its confusing for me and its not clear. ok can you please look into my reply #32

 

[vletm]

@Aswinraj
There are NO JUST LOCKED ...
I have tested that with previous setting, if some setting 'like just lock' want to use, that kind of 'small' = IMPORTANT feature HAVE TO KNOW BEFORE start to make code.
Ex1, 'Home'-sheet do not work with 'new settings' as it has planned to work!
Ex2, 'Sheet2' has STILL range with instructions. I just checked .. 'LOCKED'

Of course, You can do what ever You want to do, but sometimes Excel cannot follow You wants.
If something ... I could check about 6 hours later something, so far try to get ideas.
Gotta go to swimming now ...

 

[Aswinraj]

@vletm, i diagnose the issue, it was with Database sheet. When Protect the sheet and i Lock the cells from editing but allow use to copy the data then the error occurs in LOGIN stage and its not saving.
Can you fix this???? Since macro should work when the excel is protected and Cells are Locked right...,

 

[vletm]

@Aswinraj - try to read this
1) 'Non Auto Log Out' items: You can NOW use those as IN THIS FILE.
You have added 'CATEGORY' to Cell[B1] ... now, Users can select that item too. No challenge for me. Next part is for those 'new items'. You can add as many as You want! Those HAVE TO BE BEFORE 'normal items'. Questions? No matter of color! After You have add/change... those 'new items', You HAVE TO WRITE NUMBER OF THOSE 'NEW ITEMS' to Cell[E2]. If no value then ALL items will make 'Auto Log Out'-feature. Questions?
2) Your 'Locking'! You noticed something, good.
I try to tell next, which HAVE to BE as I HAVE DONE!
2a) 'HOME'-tab: ONLY Cell[K6] can be UNLOCKED, OTHERS are LOCKED. If You need passwords, use but don't change any settings! If something other needs, You can ask - what will happen if...
2b) 'Sheet1'-tab: You can do what You need ... anyway data validations need to modify.
2c) 'Sheet2'-tab: Range(H1:K1) HAVE TO BE UNLOCKED!
2d) 'Query Tracker': You can do what You need.
2e) 'Database': Range[A:G] HAVE TO BE UNLOCKED! If You need password, use. Those Columns HAVE TO BE POSSIBLE TO EDIT!
>> Ideas ... Questions?

 

[Aswinraj]

@vletm, i read your points.., My Understanding as below.
1. If i need to add new items for Auto logout validation then i need to add it in Cell B2 and i need to mention the number of items to E2.
Example: you had given as 5 in E2, if i add 1 item in B2 then i need to change E2 as 6.., right? Other items in Black color font will act as Non Auto Logout. right?
2a. Yes I need a password to protect HOME Tab . Password: Aswin-02
2b. One more change i noticed, in Sheet1 (A, B Columns) dropdown includes A1,B1 headings from Sheet2 which should not be.., Those dorpdown should contains items from A2,B2 from Sheet2.
2e. Database: No matters if it has to be Unlocked , as i said before those columns needs to RESTRICT USER from EDITING those data and ALLOW to COPY the Data.

 

[vletm]

1) Now You have 'non Auto' [B2:B6]. If You want to add one more, You move [B7:B34] one step below and add Your new one in [B2:B7] also update [E2] to value 6. If You want to delete one then You clear that item, keep those 'non Auto' together and You move [B7:34] one step above, also update [E2] to value 4. There is NO MATTER with colors of items, maybe easier to see 'non Auto' and normal items.
2a) 'My file' has no password. Take protection off and set it on with Your password.
2b) Dropdowns: there were NO headings for long time, just few days. You should change/check ALL DROPDOWNs ... or at least those Your ranges.
2e) Do You know which was 1st egg or chicken? This is not same kind of question! Those columns need to be possible to write! If not possible to write, then those will be empty. I remember very well that You wanted that users can copy, but You could not understand that if not possible to edit then there are blank columns. I offered one possible 'dropdown', but You wrote no! I can offer on more thing ... You can test it.

 

[Aswinraj]

@vletm,

1. I will circulate this sheet to my team and test this case.
2a. it seems even if i protect the sheet with password then there will be no errors.
2b. I changed the ranges for the dropdown and now the headings are not reflecting.
2e. I think its ok as of now, let me check finally alltogether when other errors are fixed.

Error: When i choose the dropdown in Sheet 1, this error occurs and i havent modified any thing other than dropdown range in sheet2, just to avoid headings in the dropdown present in sheet1.

 

[vletm]

@Aswinraj
1) These needs testing ... testing .. testing
2a+b) As I wrote
2e) I made one change.
Error.) Solved! I used my file again ... so You gotta do (2a+b) again.
Anyway, there is one line, just above that 'error',
which You would remember or do something for it
'CODED BY ASWINRAJ A

 

[Aswinraj]

@vletm,
1. 2a+b: I just informed you what is the action taken from my end.
2e. Now no error.
'CODED BY ASWINRAJ A has been added at the initial stage when i created this page and added some codings and forgot to remove it after when you added the code.

Will get back to you once tested all the items on the whole.

 

[Aswinraj]

@vletm,
Found another issue, I need to erase Saved Old Data from the Database before circulating this sheet to team members.
But when i remove the data from Database, and Do the Login, Logout action., the Logout is not working properly. Attached the same sheet for your reference to fix this issue.

 

[vletm]

@Aswinraj
I testing that with my file ( no time to compare ).
1) Go to 'Database'-sheet
2) Scroll the 1st left row as top as possible
3) There is 'clean'-text in top row
4) Activate that cell
5) If You want to clean rows, write '1357924680'
>> There is limiter to see Log In/Out rows,
if 'too many (~about 350), it's better to clean
There are formulas in those visible cells,
so if someone DELETE/COPY and so on ...
no more information to see.
> Ideas ... questions?
Gotta go!

 

[Aswinraj]

@vletm, I tried but this is not good as expected.

When i enter Activate the Clean - text cell and enter the number "1357924680" it ask as "Clean rows before 9", if i click yes then i removes only some rows of data but not all.

It should be as ' Removing all the data from the Database sheet'

Example: when there is Clear Button and when it is clicked, all the informations saved should be removed. like wise..,

 

[vletm]

@Aswinraj
It is as good as You asked, it's better!
You can CONTROL how many rows want to delete!
You didn't follow instructions!
2) Scroll the 1st left row as top as possible
Which row did You select?
Did You select the 1st empty row or something else?
Why to try to write step by step instructions at all?
Why to make those 'safe questions' at all?
You asked possibility to erase Saved Old Data, nothing more!
I gave more!

 

[ashish mehra]

@Aswinraj

One question arise to my mind; how can you protect or secure others from modification as excel is not a safe technology when it comes to security breach.

BTW: You can have a look at http://www.onlinepclearning.com/excel-userform-login/

Regards,
AM

 

[ashish mehra]

Try attached file.
User Credetials for Administrator
Id: Admin
Pwd: 1234

You can go to Login worksheet & control the users; create new user, etc....

Regards,
AM

 

[vletm]

1st test) Three times wrong login attempt gives ... something missing!


2nd test) That file 'opens straight to 'Login'-sheet WITHOUT asking anything ... this is normal too ... for me.
3rd test) It asks to Log In. Works with Your given information.

Hmm ... Not work as well as needed!
If that gives possible to the 2nd test, users can get ALL 'secure' informations at once!

'as excel is not a safe technology':
You can only try to make modifications at least more challenge
including Protect Project too, You gotta have the automatic backup (different versions) ... You can compare codes and so on, You save real user names (not Your given; system given user names) to another file to see/control who really use Your files. You can share data to another file. There are as many 'trick' as You can only think? Ask Your NetWork Administer to set suitable settings for Your folder/files.
If You 'too much protect and so on' those make application non useful.
The application should help users, then they want that it works for them!
Sometimes it only have to trust!

 

[ashish mehra]

@vletm

1st test) Three times wrong login attempt gives ... something missing!Ans: Can't comment on this bcoz on my system its running fine. The file is automatically closing when user failed on 3rd attempt.

2nd test) That file 'opens straight to 'Login'-sheet WITHOUT asking anything ... this is normal too ... for me.Ans: Try the new attached file. Admin have the access of Login worksheet.

Anyways, I have create new user id & give you access for 3 Sheets viz January, June & Report 1

Id: vletm
pwd: 1234

Now, check what access you have as a User as compare to Admin. Hope this clear.