CODEWITHSUNDEEP
excelvbatimestamprangerow
RailMan
RailMan

Reputation: 5

VBA - Update timestamp when a row range is edited

I'm having some issues trying to put together a VBA code that will update a timestamp every time something in the row is edited.

So what I need is a time stamp in A3 for when something from B3:CA3 is edited, and so on for every row up to 1000.

I've done some looking around but all of the codes I've found so far only related to a specific column and not a row range...

Any help will be much appreciated.

Upvotes: 0

Views: 2024

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Try this worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, Intersection As Range, cell As Range
    Set r = Range("B3:CA1003")
    Set Intersection = Intersect(r, Target)

    If Intersection Is Nothing Then Exit Sub

    Application.EnableEvents = False
        For Each cell In Intersection
            Range("A" & cell.Row).Value = Date & " " & Time
        Next cell
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

EDIT#1:

To get both the NT username and the application username, try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, Intersection As Range, cell As Range
    Dim s As String

    Set r = Range("B3:CA1003")
    Set Intersection = Intersect(r, Target)
    s = vbCrLf & Environ("USERNAME") & vbCrLf & Application.UserName

    If Intersection Is Nothing Then Exit Sub

    Application.EnableEvents = False
        For Each cell In Intersection
            Range("A" & cell.Row).Value = Date & " " & Time & s
        Next cell
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions