date stamp when cell changed

R

richardjshaffer

Board Regular
Joined
Oct 9, 2008
Messages
84
Hi,

hope someone can help, very simply I want to capture the date and time that a cell is changed.

So if the value in cell A2 is changed, then cell B2 date stamps with the current time; similiarly if cell A3 is changed, then cell B3 date stamps...

Tried looking at previous threads, I'm a bit confused by one that seems to work with a personal sub? Please can someone help, I'm no expert in macros but if there's a simple one I can use, or a formula based solution, I'd be very grateful,
thanks, Richard
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi,

they are changing by a drop down - a data, validation, there are a number of options to populate the cells in column A on the drop downs.

We just want to capture the last time someone has changed the drop down in each column A cell.

thanks
 
Upvote 0
You can use the Worksheet_Change event:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then Cells(Target.Row, 2) = Now()
End Sub

Enter the VB editor (Alt+F11), double-click the worksheet name in the Project Explorer (left side of the VBE window) and a window containing the code associated to that worksheet will appear. In that window paste the above code.

For the code to work you need to enable the macros every time you open that workbook.

The time stamp will appear in column B next to the cell that has been changed in column A even if the value in that cell was deleted. You may delete the values in column B at any time without any consequences on the other cells in the worksheets.
 
Upvote 0
richardjshaffer,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Right click the sheet tab you want the code in, and click on View Code. Paste the below code there (on the right pane) by pressing the keys CTRL+V
3. Press the keys ALT+Q to exit the Editor, and return to Excel.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2:A1000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
With Target.Offset(, 1)
  .Formula = Now
  .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


Then make changes in column A, from cells A2 to A1000 (expand the A1000, in the above code, to the last address in column A that you will ever use).
 
Upvote 0
Suggest protecting the sheet to prevent the user from changing the date stamp. Of course un-lock the cells allowed user input.
Code: 
Option Explicit<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 <o:p></o:p>
Private Sub Worksheet_Change(ByVal Target As Range)<o:p></o:p>
    If Intersect(Target, Range("A2:A1000")) Is Nothing Or _<o:p></o:p>
       Target.Count > 1 Then Exit Sub<o:p></o:p>
    Me.Unprotect Password:="Secret"<o:p></o:p>
    With Target.Offset(, 1)<o:p></o:p>
        .Formula = Now<o:p></o:p>
        .NumberFormat = "m/d/yy h:mm:ss AM/PM"<o:p></o:p>
    End With<o:p></o:p>
    Me.Protect Password:="Secret"<o:p></o:p>
End Sub
<o:p></o:p>
I don’t thing you need
Code: 
Application.ScreenUpdating = False<o:p></o:p>
Application.EnableEvents = False
There will be no screen flicker and the If statement takes care of repeated event calls. Avoid crippling Events as much as possible
<o:p></o:p>
 
Upvote 0
Would it be possible to add a date stamp in the next 4 cells to the right pending further changes to the original cell. i.e. everytime the cell is changed a new date would be added to the next available cell.
 
Upvote 0
I'd just use a comment
<a href="http://www.vbaexpress.com/kb/getarticle.php?kb_id=909">Tracking Changes with Comments</a>

lenze
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Count number of entries that have a date stamp of last week
Replies
6
Views
431
Joe4
Joe4
D
  • Question
VBA help to create button that moves to next row after taking screenshot
Replies
1
Views
93
ranman256
ranman256
S
  • Question
Identifying changes in 2 data sets
Replies
1
Views
219
JamesCanale
JamesCanale
B
  • Solved
Stop UDF/code from updating time when cell is edited
Replies
6
Views
317
Georgiboy
Georgiboy
M
  • Question
VBA to split worksheets going through motions and confirming saved in directory... but nothing there when I check!
Replies
2
Views
142
Joe4
Joe4

Forum statistics

Threads
1,214,523
Messages
6,120,047
Members
448,940
Latest member
mdusw

Share this page

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