Attend Excelapalooza
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Time Stamp Help

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Time Stamp Help

    Hello everyone!

    I'm currently working on a Macro-enabled Timesheet for my job where the employees can click a button to record the time they come in to work and leave.

    I have 4 fields that require a timestamp per day (Clock in for the day, clock out for lunch, clock in from lunch, and clock out for the day).

    I also have two additional buttons that are used to mark a particular day as a holiday or vacation.

    I'm trying to protect the worksheet so that the employees cannot alter or delete an entry so I unchecked the option to allow users to select locked/unlocked cells, but when I do so it does not allow the user to select the cell where they will have the timestamp recorded (Example below).

    Date Time In Time Out (Lunch) Time In (Lunch) Time Out
    06/08/2018
    06/09/2018

    This is the code that I have for the Clock In/Out button:

    Sub EnterTime()
    ActiveSheet.Unprotect Password:="password"
    Dim DT
    '
    ' MyTimeStamp Macro
    '
    ' Keyboard Shortcut: Ctrl+t
    '


    DT = Format(Now, "hh:mm:ss AM/PM")
    ActiveCell.Select
    Selection.NumberFormat = "hh:mm AM/PM"
    ActiveCell.Value = DT
    ActiveSheet.Protect Password:="password", _
    DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

    The issue that I am having is that it is not entering the timestamp in the cells that it needs to be entered in. The only way I have been able to make it work is when the sheet is not protected and the user can select the cell where the timestamp needs to be entered, but in doing this the user can still delete the entry.

    Does anyone know how I can get this to work?

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    5,887
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Time Stamp Help

    Make sure that all the cells which will contain the time stamp are unlocked. Then protect the worksheet, select a cell and run this macro:
    Code:
    Sub EnterTime()
        ActiveSheet.Unprotect Password:="password"
        Dim DT
        DT = Format(Now, "hh:mm:ss AM/PM")
        ActiveCell.Select
        Selection.NumberFormat = "hh:mm AM/PM"
        ActiveCell.Value = DT
        ActiveCell.Locked = True
        ActiveSheet.Protect Password:="password", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    5,887
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Time Stamp Help

    This is another approach that might work better for you. Assuming that your time stamps will go in columns B to E, unlock all the cells in those columns. Protect the worksheet with the word "password". When you protect the sheet, unclick "Select Locked Cells". Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. You can change the column target range to match the four columns that will contain the time stamp if necessary. Close the code window to return to your sheet. Double click in any cell where you want a time stamp.
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Intersect(Target, Range("B:E")) Is Nothing Then Exit Sub
        ActiveSheet.Unprotect Password:="password"
        Target = Format(Now, "hh:mm:ss AM/PM")
        Target.Locked = True
        ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub
    Last edited by mumps; Jun 11th, 2018 at 01:06 PM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    New Member
    Join Date
    Jun 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Stamp Help

    Thank you!

    This works for the first cell, but when I click the button to "Clock Out" it doesn't move to the next cell, it just updates the same cell (in this case B6). Any tips on how to fix this?

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    5,887
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Time Stamp Help

    Have you tried my second suggestion in Post #3 . I think this will make it much easier for you.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Stamp Help

    Never mind, it's not working

    I added the code you suggested in Post #3 and linked it to the button but it's not working. I get an error message: Argument not optional.

  7. #7
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    5,887
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Time Stamp Help

    The macro I suggested will trigger automatically when you double click the cell. You don't need the button. Please follow the instructions in Post #3 .
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  8. #8
    New Member
    Join Date
    Jun 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Stamp Help

    Ok, I followed the instructions. Now it is requesting the password, so I entered it and it enables the cell to be edited. However, the purpose of this is to prevent the employees from editing the cells, we only want them to be able to record the time they clock in and out with the click of a button.

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    5,887
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Time Stamp Help

    Did you unlock the cells which will hold the time stamp, in this case columns B to E? If they are unlocked, you will not be asked for a password. When you protect a sheet you do not have access to any cells that are locked. If you unlock them, you do have access even though the sheet is protected. The way the macro works is that it will be triggered automatically when you double click any cell in columns B to E. When you double click the cell, the time stamp will be entered automatically and the cell will be locked so that it can't be modified. Please follow the instructions carefully otherwise the macro won't work. I tried it on a dummy sheet and it worked perfectly.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    New Member
    Join Date
    Jun 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time Stamp Help

    You were absolutely right, it does work! I'm sorry for all of the back and forth, it is working perfectly now.

    Thank you for all your help.

    One more question, do you happen to know if there is any code that can be added to record the computer's IP address or name?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com