+ Reply to Thread
Results 1 to 26 of 26

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

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

    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!!
    Last edited by TruBeast; 02-28-2013 at 09:38 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    Quote Originally Posted by TruBeast View Post
    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.
    Attached Files Attached Files
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  3. #3
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Haha WOW!!!

    Thanks heaps mc84excel!!! I cant believe how much shorter, simpler and easier to read your version is!!

    I don't yet quite understand how the "with" command works, or what the "sht_input_cntrctr" is (I am assuming it is referring to the sheet it is taking the data from, but I don't understand where the reference is coming from) but I will definitely be researching it more.

    Thanks a lot for the tips - will definitely take them on board.

    Got so much to learn still, every time I think I have learnt something great in VBA, something better always shows up.

    Again, thanks for taking the time to look at my file, and for the advice you have given, you are an absolute legend. If you could please help me out with my logout problem when you have a chance it would be greatly appreciated.

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

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

  5. #5
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Quote Originally Posted by mc84excel View Post
    I have sent a private message to TruBeast (re With and coded sheet names) to avoid derailing this thread.
    Thanks mc84excel.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

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

    @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
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  7. #7
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Quote Originally Posted by TMShucks View Post
    @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.
    The info mc84excel provided me with was really helpful, I certainly wasn't aware of it, and its definitely something I am going to use from here on in .

    If it will help others, by all means feel free to post it here, so long as it doesn't lead away from my initial question, 'cause I really, REALLY need help with that

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

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

    @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.
    Attached Files Attached Files
    Last edited by mike7952; 03-01-2013 at 03:28 AM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  9. #9
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    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!
    Last edited by TruBeast; 03-01-2013 at 02:26 AM.

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

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

    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.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Thats Perfect!!

    Thanks Mike - so sorry for the trouble.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

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

    workbook without saving it.

    Mike,
    Bill Gates wants you to buy excel 2010, even 2013 and the problem sorted. You can recover any unsaved file in 2010.

  13. #13
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

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

    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
    Attached Files Attached Files
    Last edited by mike7952; 03-02-2013 at 09:18 PM.

  14. #14
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Quote Originally Posted by mike7952 View Post
    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
    Mike, Version4 is amazing! Thank you!

    I actually had no idea forms could be created and used like this! I still have no idea how it works, but it is definitely something I really want to learn now - especially the "add new contractor" form, so cool!

    Because of your great work, it has now left me with more questions - sorry I have tried to solve them myself, but I would have no idea where to start, as I said I have never worked with forms like this before, and your script is way to advance for me (although I o plan to study it thoroughly to learn how it is working ).

    1. At the moment in the "add new contractor" form I can click on the "add contractor" button without inputting information into any of the fields (which creates blank entries). In my original file I had:

    Please Login or Register  to view this content.
    Is there any way something like this can be incorporated into the form you have created(so that the admin has to put at least first name, surname name and password before proceeding)?

    2. eventually there will be 14+ contractors on record, so rather than select their names from a list in the "log-in/log-out" form, can there be a function added that allows them to type their names? (eg - if John Smith wanted to log in he would type "s" and Jack Smart, John Smith and Johnathon Smithy's names would appear, if he continued typing his surname "smi" then only John Smith and Johnathon Smithy's names would show because Jack Smart's name is "sma").

    Again, thanks so much for your help mike.

  15. #15
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

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

    (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.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    02-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    19

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

    Thanks Mike, works a treat

  17. #17
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    Quote Originally Posted by TMShucks View Post
    @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).

  18. #18
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    Quote Originally Posted by TMShucks View Post
    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.

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

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

    @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

  20. #20
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    Quote Originally Posted by TMShucks View Post
    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).

  21. #21
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    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).
    Attached Files Attached Files

  22. #22
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

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

    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.
    Attached Files Attached Files

  23. #23
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    Quote Originally Posted by mike7952 View Post
    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).

    Quote Originally Posted by mike7952 View Post
    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 )

    Quote Originally Posted by mike7952 View Post
    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')

    Quote Originally Posted by mike7952 View Post
    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?)

  24. #24
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

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

    @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

  25. #25
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

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

    Quote Originally Posted by TMShucks View Post
    @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.

  26. #26
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1