1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Timeclock

Discussion in 'Business Applications' started by ckitchen, Jan 24, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. ckitchen

    ckitchen Thread Starter

    Joined:
    Jun 18, 2010
    Messages:
    28
    I am trying to use excel as a time clock. If I make the macro =NOW(), everytime they click the button to clock in or out, it updates the prior cells. Is there a way to make the macro equal to cntrl+shift+: so that it wont change each time?

    I am also trying to get it to advance to the next cell instead of coming back up the the same cell when you hit enter
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    If you use date() instead of now and time() in 2 cells it will not change if you open the sheet again because now() is now at any moment

    Try it and let us know.
     
  3. ckitchen

    ckitchen Thread Starter

    Joined:
    Jun 18, 2010
    Messages:
    28
    I tried =date(), =time() and I do not get the date or time. I obviously do not know how to use this formula. Can you show me an example?

    When I do =now() I get date and time

    When I do =time() I get the formula you typed contains an error :mad:
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Can you post a sample of what you have?

    I need to know what and how the 'button' works and see wha'ts wrong
     
  5. ckitchen

    ckitchen Thread Starter

    Joined:
    Jun 18, 2010
    Messages:
    28
    I think I got it attached
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Okay, got it. will see what I can do for you
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    I don't have Excel 2007 here but you you test the following:

    Copy and paste the following code in the Sheet's VBA project:

    Code:
    Option Explicit
    
    Private Sub Worksheet_Activate()
        If Range("B2").Value <> Date - Weekday(Date, vbMonday) Then Range("B2").Value = Date - Weekday(Date, vbMonday)
        Range("C" & Range("C31").End(xlUp).Row + 1).Select
        If ActiveCell.Row >= 31 Then Range("C1").Select
    End Sub
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    If Target.Row >= 3 And Target.Row < 30 Then
        Target.Value = Time()
        Target.Offset(1, 0).Select
    End If
    End Sub
    
    
    What this does is when that particular sheet is selected the first empty cell in column C is activated
    Double click on that empty cell and the current time is entered.

    Another point is that the value in cell B2 should not be =now() but also activated by the vba code
    I did that too it now sets the value of the Monday of the active week
    You name it Pay Date so maybe you should change it to something else but it just serves to point out what you can do.

    See whay you can do with this code.

    I pasted it in the VBA project for Alys, you can ignore the button, I didn't do anything with that

    You will have to calculate the Monday of that date and set it here
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    I have added the code here at home in Excel 2007 and it seems to do the job in all sheets.
    I have attached the sample.
     

    Attached Files:

  9. ckitchen

    ckitchen Thread Starter

    Joined:
    Jun 18, 2010
    Messages:
    28
    That's great! Is there anyway so that they cannot change the time by typing in and only be able to dbl click it?
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Yes this is possibel by protect the worksheet (with a password) adn use the code to unprotect, enter the value and then protect it again.

    I was just wondering:
    1. what do you do when the week is over? Print each sheet and then clear it?
    2. Does everyone use this same sheet?
    3. Who's the administrator?
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    I kept on grinding the idea and came up with some additions.
    I did however standardize your sheets.
    There is an Admin Sheet with three columns, Employees, Description and ResponsibleCo

    Your validation ranges are no longer sheet dependant, the Named ranges are set whe you add an Emplyee in this sheet or a Description or a ResponsibleCo

    After you add one or delete a cell then you must double click on the header and the column iss sorted again and the named range is updated so when you go to one of the sheets the new choice becomes available.

    I have to delete your formula's for If H4="Coater"then "Qustel" that's hand work

    The Employee sheets are now unprotected but when you press the button to clock the time is entered in the first empty cell and the shtee's protecdted for the time settings.

    The password is hardcode in the VBA project and the variable is guess and is = 1234 if you want to unprocetd the sheet manually.

    Once all this works like you want it then the advise is to protect the VBA project with a password and make ik invisible to the suer unless he / shee knows the password for the VBA project.

    Maybe this goes too far for now but it's an idea for what you can do.

    I do check if a new week is started but no action there excep changing the Monday's date to the corresponding week.

    More coding to do there.
    Here's the sample and just take a look and see what you can make of it
     

    Attached Files:

  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    I acme accross a typing error in the code which could generate an error. :eek:

    I corrected this and attach a new file :)

    I'm curious to read your findings
     

    Attached Files:

  13. ckitchen

    ckitchen Thread Starter

    Joined:
    Jun 18, 2010
    Messages:
    28
    That's awesome. I already implemented the other sheet but i might dabble with this one when i get a chance. Thanks!!!!!
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/976686

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice