Auto Stamp Date

[ShePink77]

I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in cell
E1? If so can you help me understand how to write the formula?

 

[Alan]

You can't do it with a formula because the NOW() function will update every
time the workbook calculates, not just when you alter B11
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B11")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.

 

[ShePink77]

Alan, thanks so much for the help. I have found the Alt F11 but where do I
copy and paste the below formula once the VBA Project window is open?

 

[Alan]

Click the 'VBProject' icon on the top left,
You should see 'Microsoft Excel Objects'
Under that are three icons, Sheet1, Sheet2 and Sheet3
Double click the sheet you want this in and copy and paste the code into the
new blank window in the middle,
Post back and tell us how you got on!
Alan.

 

[Gord Dibben]

Easiest method to add event code to a sheet is right-click on the sheet tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

 

[DataGuy]

It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2 to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

 

[Alan]

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger? If
you did, resolve it, save the file and reopen it,
Regards,
Alan.

 

[ShePink77]

Alan, I did get the formula to work the trick was to save my spreadsheet and
then reopen. One last question how do I copy and paste this formula to all
of column E? For example: If I make any update in a specific row only the
date will auto stamp in column E of that same row.

 

[EMW103]

Folks, I had the exact same question yesterday. I tried the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for me?

 

[Alan]

Try this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then
Range("U10:U499") = Now
Application.EnableEvents = True
End If
End Sub

You have

If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then

It should be

If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then

Regards,
Alan.

 

[EMW103]

No luck, Alan. Here's the situation: if a cell in column V has a currency
amount in it (which is the result of a formula involving other columns), I
want a date stamp in the corresponding cell in column U. Which is why I
wonder if the programming below, which indicates that column V should have
"Nothing" is correct? My original formula for column U, for reference, was
=IF(ISNUMBER(V10), NOW(), ""), but NOW produces a constantly updated date
every time you open the document, which I don't want.

 

[Alan]

It's not column V that is Nothing, it's Application.Intersect(Taget......
that's Nothing.
As I understand it, you want an individual date stamp as the result of a
formula in the adjacent cell. This code won't do that, it will only work if
the cell has been changed by the keyboard, a change made by a formula by
entering something into another cell won't trigger it.
I don't know how to do that I'm afraid, you should ask in Excel Programming,
Good luck,
Alan.

 

[Gord Dibben]

If your changes in V10:V499 are the results of formulas you must use the
Worksheet_Calculate event, not the Sheet_Change event.

Private Sub Worksheet_Calculate()
the code
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

How are the changes in A2:C2 being made?

The Sheet_Change code works only when a manual change is made in any of
A2:C2 cells.

I tested the code you posted under those circumstances and is OK.

If A2:C2 are formula-dreived values then you must use a Sheet_Calculate
event.

Private Sub Worksheet_Calculate()
code
End Sub


Gord Dibben MS Excel MVP