Auto fill date and name of users when they change cells in a row.

[Frott81]

Hello

I have been trying to find a macro that autamatically fills 3 different cells with date, updated date and name of user, when someone fills certain cells in a worksheet.

i have 2 macros that work independently. but i cant make them work together:
(Optimally i would like to have the macro for username, fill C7 with username when B7 is changed and so on down to B307) WE are 15 peaople who uses the worksheet and its nice to see whos done the changes.



----------------

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
'   TeachExcel.com

Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range

'Your data table range
Set myTableRange = Range("B7:F306")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

'Column for the date/time
Set myDateTimeRange = Range("N" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range("O" & Target.Row)

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then

    myDateTimeRange.Value = Now

End If

'Update the updated date/time value
myUpdatedRange.Value = Now

'Turn events back on
Application.EnableEvents = True
End Sub

And

Private Sub Worksheet_Change(ByVal Target As Range)
 'By Excel 10 Tutorial
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B7:B306")) Is Nothing Then
With Target(1, 2)
.Value = Application.UserName
.EntireColumn.AutoFit
End With
End If
End Sub

 

[sykes]

Hi, and welcome to Mr. Excel!

As an alternative idea, if you have one of the more recent versions of Office (it may only be available in Office 365, but I'm not sure) you could forget the need for several different cells to hold the data for the editor's details; instead you could use "Threaded comments" which give the user's name, the date, and the time. This way, you'd have not just the most recent edit details, but all of them - and without using extra columns! To see a cell's edit history, you'd just need to hover the cursor over the cell in question.
The user could also edit their comments, if they wished to explain in more detail, what they'd done, and why.

Here's some info on threaded comments.

I wrote this bit of code for you, to put threaded comments into any cell edited in your table B7:F306 (hope I've understood correctly):

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If Intersect(Target, Range("B7:F306")) Is Nothing Then Exit Sub
        If Not .CommentThreaded Is Nothing Then
            .CommentThreaded.AddReply "CELL EDIT!"
            Else: .AddCommentThreaded "CELL EDIT!"
        End If
End With
End Sub

NB - remember to test on a COPY of your work first.

Hope this helps.

 

[Frott81]

Hello Mr Sykes.

I Tried your solution. it works but i still would like to have the setup that is in the sheet, if its possible.
Since we are 20 different people that should work with the same sheet. and its easier to se hows done what, rather than holding the mouse over.