Find "LOGGED IN" person and record their "LOG OUT" time next to them

Hi guys,

First of all, this is my first post, so please bare with me .

I am creating an excel file which will allow contractors to punch in their unique 4 digit contractor ID to log in at the start of their shift (which will automatically record the date and time that they log in) and then records the time that they log out when they put in their 4 digit ID and click on the "Logout" button.

I think I have the "logging in" section working ok. My problem is the "logging out".

Because the contractors will not log out in the same order that they logged in and a lot of shifts will overlap, I need a macro to put the logout time of a contractor with a specific ID next to where they logged in when they click on the logout button (... I hope that makes sense...).

I have attached an example of the file so that it makes more sense. contractors.xlsm

You will also probably notice that I am quite new to VBE - most of what I know I taught myself from reading through these forums. It seems to me that even though my macros do what I want them to do, they are really long and winded, I think there would be an easier and shorter way to get them to do what I want them to do - and I know that the shorter your macro is the better, so any tips or advice while looking at my file would be greatly appreciated!

Thanking you all in advance!!

Originally Posted by TruBeast

any tips or advice while looking at my file would be greatly appreciated!

G'day

I don't have time to solve the output time problem right at the moment (I will see what I can do next week) however I have updated your AddContractor sub and added worksheet names (see updated file).

A few tips for you from another (mainly) self-taught VBA user:

• If you are referring to sheets, you should alwaysuse the coded sheet name. Using the visible tab name is an accident waiting to happen (what happens if the tab is renamed?)
• Avoid using Select statements. It is too slow (because it is copying the data to clipboard) and unnecessary.
• If you are making multiple changes to the same object, try using With. It is faster and the code is easier to read (provided you indent )
• It is not necessary to store all your macros in one module. Try grouping related subs into different modules.

I have sent a private message to TruBeast (re With and coded sheet names) to avoid derailing this thread.

@mc84excel: others may benefit from your description of sheet codenames and their definition and use. I don't think it would derail the thread. Possibly a useful insight as not everyone will be aware of them.

You could also post in Tips and Tutorials.

I don't think that Select copies anything to the clipboard. However, it does cause Excel to select the sheet and/or cell and redraw the screen ... hence the reason for using ScreenUpdating=False

Regards, TMS

@TruBeast

Well I started on this and got busy with something else. I finally got back to it and attached is what I came up with.
I changed the names of the sheets so I could keep track of what was what. You can change them to whatever you want to, just change the Const's at the top of the Module. Created a functions to check if a user is logged in already to prevent from double log in's. Also a function to check if user in logged in to be able to log out.

AGHHHH!!! YES!!! WOOHOO!!!

Thanks mike!! It works exactly how I wanted it too!!... LEGEND!

...now I just got to slowly work through the coding to try and understand it better I really want to be able to get to a level that you pros are at with my coding so that I can start offering other people help rather than having to ask for it all the time. Now that I have it in a clean, working format (haha it was a train wreak when I posted it compared to what you and mc84excel have done with it) I'll try my best to read through it slowly and work out how it is working.

Just one more question - probably something simple for you guys - but would take me a few months to work out - is there a way when after they log in it shows them a window with something like "John Smith logged in at [date and time they logged in], and then again same thing for when they log out? And when they click the "ok" button on that window it takes the screen back to the "main menu" ready for the next user?

Thanks again Mike - you have no idea how much this has helped!

Man I hate when I get some code done and tested then close the workbook without saving it.
Anyway lol see if this will work for you.

TruBeast

Your Welcome,

I played around with your workbook some more and added a userform to clock in and out. Let me know if you have any questions. I would use a password to log in log out.
See Contractors Ver_4

(eg - if John Smith wanted to log in he would type "s" and Jack Smart, John Smith and Johnathon Smithy's names would appear

Regarding the above, I added a combobox. If you type the first letter of there surname it will goto the first surname in the list. If you continue to type s it will loop thru the other surnames that begin with s. Or you can type s then select the name you want from the list.

I also updated the Add-New to check for First and Surname has been entered. Was already checking for passwords.

Originally Posted by TMShucks

@mc84excel: others may benefit from your description of sheet codenames and their definition and use. I don't think it would derail the thread. Possibly a useful insight as not everyone will be aware of them.

I'm not very confident as to the best way to explain sheet codenames. So I will point you here instead: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
IMHO sheet codenames is an important concept in VBA that should be learnt by anyone wishing to progress beyond the macro recorder level of understanding.
(Using sheet tab names is an accident waiting to happen).

Originally Posted by TMShucks

I don't think that Select copies anything to the clipboard.

Strange. I was of the understanding that using Select to copy & paste was placing the data on the windows clipboard in the background? I have read this in multiple sources. One example: http://excelexperts.com/copy-values-vba

In any case, I believe that Select is a much slower method to copy & paste data.

@mc84excel: I am not sure what, in that blog, you are interpreting as saying Select is copying anything to the clipboard. Selecting a cell in VBA is the same as simply clicking on it with your mouse in the worksheet. That doesn't copy anything anywhere. What you often see in recorded macros is Range("A1").Select followed by Selection.Copy then the selection of another sheet/cell and ActiveSheet.Paste. That does copy to the clipboard and retrieve it. This copying to the clipboard can be avoided with code like: Range("A1").Copy Range("Z1").

You are absolutely correct in saying avoid Select but not because it is copying to the clipboard. It's because you are making Excel go through unnecessary steps. Excel does not need to select a cell in order to manipulate its values, formulae or formatting.

Regards, TMS

Originally Posted by TMShucks

Selecting a cell in VBA is the same as simply clicking on it with your mouse in the worksheet. That doesn't copy anything anywhere. What you often see in recorded macros is Range("A1").Select followed by Selection.Copy then the selection of another sheet/cell and ActiveSheet.Paste. That does copy to the clipboard and retrieve it.

I agree on everything you said in this post.

Sorry this is all my fault - I took your original quote out of context. (I read it to mean that using Select NEVER uses clipboard - whereas, as we both know, using Select to copy/paste IS using the clipboard).

I have been playing with the version 5 workbook... See attached to see if you like?!

(If you can't get the shift login/logout button to work, well neither can I. I can't get this button to work in the vanilla ver 5 workbook either).

I don't know exactly what your wanting to do. You need to address the issue when a user hits the X button on the first form that pops up and hides excel. Me personally would not use a workbook that hides excel.

See if attached will allow password to work for you. I don't think you need the below

Please Login or Register  to view this content.

Originally Posted by mike7952

You need to address the issue when a user hits the X button on the first form that pops up and hides excel.

Ouch. I missed that (As per PM sent earlier, this workbook is only a beta version, I still have changes I intend to make to it).

Originally Posted by mike7952

Me personally would not use a workbook that hides excel.

Hmmm, it's not that scary. If something goes wrong, you can easily restore Excel visibility merely by opening another file. (If you think that is nasty, try using workbooks that restrict ribbon controls/tabs & mouse controls )

Originally Posted by mike7952

See if attached will allow password to work for you.

Still not working. All buttons etc work but not the Shift Login/Logout button ("Compile error: Can't find project or library" . It occurs on Sub 'MyCollection' on line 'oDic.Item(Trim(aArr(i, 1))) = w')

Originally Posted by mike7952

I don't think you need the below

Well I cant access the VBA Tools References on the PC I'm currently at. So I am trying to programatically add the MS Scripting Reference library. (I am new at Scripting.Dictionary but as far as I can tell this library is required to use the Scripting.Dictionary?)

@mc84excel: you appear to be hijacking TruBeast's solved thread.

I suggest you start a new thread of your own and cross refer to this thread. That will generate new interest and may help you solve your problem(s).


Regards, TMS

Originally Posted by TMShucks

@mc84excel: you appear to be hijacking TruBeast's solved thread.

@TMShucks: Not so. (And I am sorry if it appears that I am hacking the thread). I was merely:

1. Offering TruBeast a more impressive version by adding a welcome form with the buttons on it.
2. Querying Mike7952 as to the solution he created because I couldn't get part of the code to work.

I am not going to start another thread as I have no use for this workbook for myself.

If TruBeast isn't interested in jazzing up the current solution and if all of the buttons work for TruBeast then I will drop the whole thing.

This will be last post on this subject, come what may, as we're kind of getting off topic and the OP had marked the thread solved, stating "Thanks Mike, works a treat" back in post #16.

@TMShucks: Not so. (And I am sorry if it appears that I am hacking the thread)

Kind of does look like that. I used to work for some large organisations and believed in the maxim "perception is reality". In other words, if your customers think and say your customer service is crap, it really doesn't matter what MI you have to prove that it's all they ever wanted. In this case, you have, undoubtedly, hijacked the thread.

Offering TruBeast a more impressive version by adding a welcome form with the buttons on it

I rather thought Mike's solution was quite impressive. So much so, I repped him for it. And I downloaded it to keep as an example of using buttons and forms as, in particular, forms are not a strength of mine. I have to say that using Dictionaries is also not a strong point ... and one I still have to work on.

Querying Mike7952 as to the solution he created because I couldn't get part of the code to work.

I could add contractors, log in, log out, etc. All seemed OK. The OP seemed happy with the solution and signed off.

I am not going to start another thread as I have no use for this workbook for myself.

Fair enough. We should draw a line.

If TruBeast isn't interested in jazzing up the current solution and if all of the buttons work for TruBeast then I will drop the whole thing.

Also, fair enough. I guess if he wants it, he'll come back on it. For me, personally, I cannot stand it when someone feels they should take over my working environment. Given that I can have a lot of workbooks open at any time, I really do not appreciate it when they are all hidden from me. Words cannot express how that makes me feel. So, clever idea, but throw it in the bin.

As I said at the outset, this will be my last response to this thread so, unless you're keen to have the last word ... let's just draw that line and walk away friends.

Regards, TMS