Excel

Add date or time to a cell when a row (or column) is updated

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

mdmackillop

Description:

The current date or time will be inserted in a selected row or colum whenever a single corresponding cell is changed. 

Discussion:

Automatically record when data changes have been made to a row or column within a spreadsheet. Note: This is not designed to record multiple cell changes or deletions 

Code:

instructions for use

			

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) SetDateRow Target, "D" 'or 'SetDateCol Target, 5 End Sub Sub SetDateRow(Target As Range, Col As String) If Target.Cells.Count > 1 Then Exit Sub Application.EnableEvents = False Cells(Target.Row, Col) = Int(Now()) Application.EnableEvents = True End Sub Sub SetDateCol(Target As Range, Rw As Long) If Target.Cells.Count > 1 Then Exit Sub Application.EnableEvents = False Cells(Rw, Target.Column) = Now() Application.EnableEvents = True End Sub

How to use:

  1. Open the VB Editor Alt + F11
  2. In your VBA Project, open Excel Objects and double click on your selected sheet
  3. where you wish the change dates to be recorded.
  4. Copy the code and paste it into the Worksheet module.
  5. Choose to run DateRow or DateCol by commenting out the redundant line
  6. Set the Row or Column value
  7. Choose Int(Now()) to record the date or Now() to record the time
  8. Save and close the VB Editor
  9. Format the selected row or column to display your values correctly.
 

Test the code:

  1. Enter data in any cell, other than the recording row or column; the date/time should appear in the corresponding cell.
  2. In the example, the two Sub procedures have been placed in a standard module, where they can be called from any worksheet.
 

Sample File:

TestDate.zip 7.54KB 

Approved by mdmackillop


This entry has been viewed 531 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express