Tech Support Guy banner
Status
Not open for further replies.

Excel Macro to Enter Username and Current Time

1K views 1 reply 2 participants last post by  XCubed 
#1 ·
Hello All,

I am trying to put a macro together that will be triggered when a value is entered into a cell. The macro would then place the current time/date in a different cell along with placing the username used in another cell. I have figured out how to have the cell event to be triggered using the below code but I am lost after that. Any help you can provide would be great.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, MyCell As Integer
Set VRange = Range("D:D")
If Union(Target, VRange).Address = VRange.Address Then

ActiveCell.Offset(0, 5).Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -5).Range("A1").Select
Application.CutCopyMode = False
End If
ActiveCell.Offset(0, 6).Range("A1").Select
Dim sUserName As String
sUserName = GetNTLoginName
End Sub
 
See less See more
#2 ·
Hi

I couldn't quite get what the macro was trying to do. The best I can figure is that for any change in Column D you want to place in the corresponding row / columns I and H - the date/time and the user name respectively.

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'
    If Target.Column = 4 And Target.Row > 1 Then    ' restricts changes to col D from row 2 onwards
      For i = 1 To Target.Count
        If Target(i) <> "" Then   ' if you double click in a cell but do not add data the date and user name will not be added
            Target.Offset(0, 5) = Date + Time  'puts the date and time in Col I
            Target.Offset(0, 6) = GetNTLoginName  'this didn't work for me in my environment
 '           Target.Offset(0, 6) = Environ("username") 'this is what I use. Try it if the previous row didn't work
        End If
       Next
    End If
'
 End Sub
See if this works for you. If it doesn't produce the results you want post back if you need help.
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top