User Name and Time Stamp

[pritchaj]

Is there a way to put a time stamp with the users name into an adjacent cell when a cell is updated?

In case that didn’t make sense I will tell you what I need this for. We are creating new time cards that require both employee and manager approval on them. We have many remote employees and getting their signature can be a pain. If they could click a button on the time card that stamped their name and time that would be ideal. They would then send to their manager they would click to verify the time and it would stamp it as well.

[MSP77079]

Clicking a button implies (to me) you are using VBA. Not sure if that means you are using a CommandButton Control and a CommandButton_Click event routine, or if you are using an AutoShape and assigning a macro to it.

In either case, the code to do what you want is something like this:

Sub timeStamp()

    ActiveCell.Value = Now
    ActiveCell.Offset(0, 1) = Environ("USERNAME")

End Sub

If you want only the Date (instead of Date + Time), use Date instead of Now.

[pritchaj]

Thank you it works but when I protect the sheet it errors out. Is there a way to keep it protected and have the macro work without the user being able to maunually update the time stamped field?

[MSP77079]

Sub timeStamp1()
Dim ws As Worksheet

    Set ws = ActiveSheet
    ws.Unprotect Password:="whatever"
    ActiveCell.Value = Now
    ActiveCell.Offset(0, 1) = Environ("USERNAME")
    ws.Protect Password:="whatever"
    
End Sub

If you are not using a password to protect the sheet, you can remove that part of the code. The obvious drawback is that this code puts the password "out there". So, if you are concerned about security, you would need to lock the VB project itself with a password to keep knowledgeable folks from hacking it.

A (only slightly) less vulnerable method would be to use a "hidden" Name to contain the password. (You could remove the code after using it one time.)

Sub hideMyPassword()
Dim nm As Name
    On Error Resume Next
    ActiveWorkbook.Names.Add _
        Name:="pwd", _
        RefersToR1C1:="=whatever", _
        Visible:=False
End Sub

Then, use this version of the code:

Sub timeStamp2()
Dim ws As Worksheet
Dim pwd As String
Dim nm As Name
    
    Set nm = ThisWorkbook.Names("pwd")
    pwd = Right(nm.RefersTo, Len(nm.RefersTo) - 1)
    Set ws = ActiveSheet
    ws.Unprotect Password:=pwd
    ActiveCell.Value = Now
    ActiveCell.Offset(0, 1) = Environ("USERNAME")
    ws.Protect Password:=pwd
    
End Sub

A person would have to be pretty knowledgable about VBA to get at your password using this. But, on the downside, this method acutally makes your password [B][I]less[B][I] secure than the above method (if the above is combined with locking the VBProject).

[whisperinghill]

I saw your post and thought what would I do. Digging around provided me two ways to provide username. 1. is the logon from the PC the one that MSP77079 provided and 2. the username from the Excel application Tools/Options-General Tab.

So I built some macros to test and made up a working timesheet, though crude at best. It only provides punch in/puchout date/Time soime math to provide total on the clock time and a weekly total. Also a cell for each day for some notes.

The more I thought about it, then there were the security issues and having the person falsifying the entries. So that required a password protect and then the option to prevent someone from overwriting the punch in accidently. These were provided from other posts.

Then since it was a remote user, an option to send in the timesheet via email and then an option for the supervisor to clear the timesheet and send it back to the user, without the user from being able to clear it.

All passwords are 1 and I even passworded the VBA also 1 and option hide the macros

Though you probably already have more than you need , I learned alot from this excercise. And I provide my sample timesheet.

[Shamz41]

Hi

Your time clock in program is impressive. Able to guide me on the VBA codes? I intend to create something similar for my department.

[JoshuaSQ]

I think I have a similar but different issue with a worksheet I am trying to develop. I would like to time stamp / insert user name whenever one or more cells in a certain range is modifed. For example:

If any of the cells from A1 - E1 are modifed- F1 would contain a date of when it was last modified and G1 would contain the user name.

this would need to be done on a row by row basis. so if a change was made in B2 - it would only effect the time stamp in F2.

Is there a way that I can do this?

Thank you

[VBA Noob]

Hi,

Does this help. Right click sheet tab and select view code then paste in the below code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x As Range
    If Target.Column =< 5 Then
    
    Cells(ActiveCell.Row, "F").Select
    ActiveCell.Offset(-1, 0) = Date
    ActiveCell.Offset(-1, 1) = Environ("USERNAME")
    
End If
End Sub

VBA Noob

[JoshuaSQ]

Thanks, that code works exactly right as I needed.

Although I have noticed a buggy thing about it.

Only when i leave a cell by pressing enter or the down arrow does it update the correct row. If i simply delete or paste, it updates the row above, and the other directional arrows also result in an incorrect row update.

Is there anyway around this, or should i just remember these concerns?

Thank you

[MSP77079]

Try replacing "ActiveCell" with "Target" (without quotes).

Target is the cell that was changed. One normally assumes that ActiveCell and Target are the same in this situation. But, apparently not all the time.

[JoshuaSQ]

I tried replacing Activecell with Target and it ended up causing excel to infinitely loop...was i only supposed to replace certain "activecell" entries?

additionally,
I was able to reverse the problem and fix the delete, paste, and left -right arrow movements- these now update on the correct row. I did this by changing the offset to 0 for row.

So now it updates the wrong row when i press down, up, or enter...
I dont know which is better.

Any more help would be appreciated.

[Parth007]

Can anyone please attach the file which whisperinghill have uploaded here.. i am not able to download it...