Time, Date & username stamp on initial data input?

[julianhall]

Hi. I have checked this thread for my answer, but it doesn't seem to quite have what i want.

I want a spreadsheet for tracking issues with the following columns:

A - Date
B - Time
C - Detector of issue
D - Details
E - Status

What i would ideally want would be for when a user enters information about an issue into a cell in column D, the date and time would be entered into columns A&B respectively and, if possible, the username (as displayed in Tools > Options) to be entered in column C.

However, i would like the initial date & time, once entered, to never change. For instance, if i were to Type "XYZ noot working" at 12:00 on 01/01/2009, then correct my typo to "XYZ not working" at 13:03 on 02/01/2009, i would like the date & time stamps still to display 12:00 on 01/01/2009.

I've got some code (below) to do the date and time but it updates with every correction i make. Plus i can't get the username to work either.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
    Application.EnableEvents = False
    Target.Offset(0, -3).Value = Date
    Target.Offset(0, -2).Value = Time
    Target.Offset(0, -1).Value = UserName
    Application.EnableEvents = True
End If
End Sub

Am i asking too much? Am i missing something obvious?

Thanks in advance guys.

 

[Magriza]

Hi,

For the username try

environ("username")

And for the time to remain the same check if there is something in the cell first.

 If target.offset(x, y).value <>"" then exit sub

 

[julianhall]

Great, that's almost it.

It appears that the username it is writing is the username from XP, rather than Excel. I really wanted the Excel username, it's more descriptive than the OS username.

Ah, a quick bit of Googling has sorted it out for me, i wanted Application.UserName rather than environ("username").

Thanks very much for your help!

 

[Joe Was]

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code only!

With Target
If .Column <> 4 Then Exit Sub

Application.EnableEvents = False

If (.Offset(0, -3).Value = "" And .Offset(0, -2).Value = "") Then
.Offset(0, -3).Value = Date
.Offset(0, -2).Value = Time
.Offset(0, -1).Value = Application.UserName
End If
End With

Application.EnableEvents = True
End Sub

 

[julianhall]

Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code only!

With Target
If .Column <> 4 Then Exit Sub

Application.EnableEvents = False

If (.Offset(0, -3).Value = "" And .Offset(0, -2).Value = "") Then
.Offset(0, -3).Value = Date
.Offset(0, -2).Value = Time
.Offset(0, -1).Value = Application.UserName
End If
End With

Application.EnableEvents = True
End Sub

That's a neater version of what i managed to cobble together with the help of Magriza:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
    Application.EnableEvents = False
    If Target.Offset(0, -3).Value <> "" Then Exit Sub
    Target.Offset(0, -3).Value = Date
    Target.Offset(0, -2).Value = Time
    Target.Offset(0, -1).Value = Application.UserName
    Application.EnableEvents = True
End If
End Sub

Thanks!

 

[Joe Was]

With events you must limit the range(s) or condition(s) to test up front and provide a way for the code to Exit automatically, or it will test everything even itself and run in a loop, untill it times out. So, keep this in mind when developing Event code!

 

[julianhall]

Ah, my code didn't do that did it? Thanks again.

 

[Joe Was]

A nother good Event limit and exit is:

If Target.Count > 1 Then Exit Sub

It tests to see if the Selection is a Single Cell selection, if not then it Exits the Event Sub!

You can stack these tests one after a nother, then add the bulk of the code below these, depending on how you want your Event to work or not work. As, users will do anything and you must be a step a head. It is like Error Trapping, you must account for all the possible earrors that could happen and address them.