Date and author stamp for several cells

[sriche01]

Hello All,

I saw several posts that were almost what I needed, but not quite. So I thought I would throw my problem out there.

I have a large macro-enabled workbook which I am trying to make more easily reviewed as there are multiple users. Two worksheets in particular have heavy user input.

What I am looking for is two columns, one that will automatically generate a time/date stamp and another that would record last user. Both of these triggered by editing cells in that row in columns C,J,L,M,N,O. So if any of these columns are edited in a given row, one cell would record the last edit time, and the other the last editor (user name).

Thanks for your help in advance!

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and add this VBA code to the resulting VBA window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
'   Loop through all cells that were just updated
    For Each cell In Target
'       Check to see if columns C, J, or L-O updated
        If (cell.Column = 3) Or (cell.Column = 10) Or _
            (cell.Column >= 12 And cell.Column <= 15) Then
'           Add stamps
            Application.EnableEvents = False
'           Update column A with username
            Cells(cell.Row, "A") = Environ("username")
'           Update column B with date/time stamp
            Cells(cell.Row, "B") = Now()
            Application.EnableEvents = True
        End If
    Next cell

End Sub

Note that you did not mention which two columns should be updated with these stamps. So I assumed A and B. Change those column references to suit your needs.

 

[sriche01]

Thank you. So far it looks like it does what I want. One question: If I insert columns will any of the column references in this code float with the expansion?

 

[Joe4]

One question: If I insert columns will any of the column references in this code float with the expansion?

No, since VBA is not stored in individual cells, they do not "float" as you adjust your workbook.
You would need to adjust this line:

        If (cell.Column = 3) Or (cell.Column = 10) Or _
            (cell.Column >= 12 And cell.Column <= 15) Then

Note that .Column refers to the column index number, i.e. "C" is the 3rd column, "J" is the 10th, etc.

 

[sriche01]

Got it! Thanks!