auto timestamp a:a when any cell in a row changes

[stolen_83]

i'm trying to get VBA to auto timestamp column a when any cell in that row changes.... any ideas....???

[ffffloyd]

You need to code for the Worksheet_Change event. Be careful that you only apply the timestamp for specific columns though---or at least exclude the timestamp column from consideration---or you will generate a new timestamp every time you generate a new timestamp!

[stolen_83]

can you provide sample code, i am new to VBA, experienced in web languages....

[Leith Ross]

Hello stolen_83,

Welcome to the Forum!

There are two macros you need to install. One is the Worksheet_Change event and the second is in a VBA Module. The second macro adds the time stamp as a Comment to the cell. If the cell does not have a Comment then one is added automatically. If the cell does have a Comment then only the time stamp is added.

Worksheet_Change Event Code

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
       Call AddTimeStamp(Target)
    End If
    
End Sub

How to Save a Worksheet Event Macro

1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Module Code - AddTimestamp Macro

'Written: December 13, 2010
'Author:  Leith Ross
'Summary: Adds a time stamp to the active cell using a comment. If the cell does not
'         have a comment one is added. If a comment exists then it is updated.

Sub AddTimeStamp(Cell As Range)

  Dim Cmnt As Comment
  Dim NewText As String
  Dim Text As String
  Dim User As String
  
    User = Environ("UserName") & ": "
    
      NewText = User & " " & Format(Now(), "dd-mmm-yyyy  hh:mm") & vbLf
      Set Cmnt = Cell.Comment
      
      If Cmnt Is Nothing Then
         Set Cmnt = Cell.AddComment(NewText)
      Else
         Text = Cmnt.Shape.TextFrame.Characters.Text
         Cmnt.Shape.TextFrame.Characters(Len(Text) + 1).Insert NewText
      End If
      
    Cmnt.Shape.TextFrame.Characters(Len(Text) + 1, Len(User) - 1).Font.Bold = True
    Cmnt.Shape.TextFrame.AutoSize = True
      
End Sub

Adding the Macro (Excel 95 - 2003)

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Save the Macro by pressing the keys CTRL+S
8. Press the keys ALT+Q to exit the Editor, and return to Excel.