CODEWITHSUNDEEP
excelvbaformula
user2591771
user2591771

Reputation: 25

Excel VBA timestamp and username

The code below detects data when inputted into column A and automatically inserts the current user into the cell to the right. I would also like this code to add a timestamp as well. I need to log the user name and time. Any suggestions? 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range

    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 1)
                    .Value = UserName()

                End With

            Else
                rCell.Offset(0, 1).Clear
            End If
        Next
    End If

ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Public Function UserName()
    UserName = Environ$("UserName")
End Function

Upvotes: 1

Views: 18493

Answers (1)

Katstevens
Katstevens

Reputation: 1571

You could just use something like date & " " & time. This will output the date and time concatenated like so:

17/07/2013 11:49:39 PM

Here is your code with the date/time value added into the next column:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rCell As Range Dim rChange As Range

On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
    Application.EnableEvents = False
    For Each rCell In rChange
        If rCell > "" Then
            rCell.Offset(0, 1).Value = UserName()   
            rCell.Offset(0, 2).Value = date() & " " & time()   <-- added line          
        Else
            rCell.Offset(0, 1).Clear
        End If
    Next
End If

Upvotes: 1

Related Questions