Date and author stamp for several cells

sriche01

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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:
Code:
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.
 
Upvote 0
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?
 
Upvote 0
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:
Rich (BB code):
        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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top