automatic date update when using sheet change

[marleydog]

I am trying to get column A to display the date that a change is made to the workbook in a corresponding row.

example: Cell A5 currently reads 1/1/05 (last update) and cell C5 shows a value of 300

If I change the value in C5 to 500 I would like cell A5 to automatically update to 2/11/05 (today date)

I found some similar code on the board but I'm having trouble getting the date to fall in the correct cell.


Thank You!!!!

 

[Norie]

Can you post the code you have tried?

And explain what is going wrong and what exactly you want to do?

Is it always the same cell for the date or does it actually based on the cell that is changed?

 

[erik.van.geit]

marleydog,

Could this give you a start?

kind regards,
Erik

Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Target.Row, 1) = Date
End Sub

 

[marleydog]

That's perfect. Exactly what I was looking for, similar code to what I tried but I was setting the target incorrectly. I'm learning, this board is excellent. Thanks for your help.

 

[erik.van.geit]

marleydog,

A little warning though (forgot to mention)

this line is also writing to the sheet
Cells(Target.Row, 1) = Date
Since this changes the sheet it involves a second "loop" through the worksheet_change code. Because then the date is written again not involving a real change the code can stop.
To avoid unexpected results - certainly necessary in other cases - use EnableEvents = False


kind regards,
Erik

 

[chiello]

Furthermore, if you want the event routine be skipped when changes involve ranges greater than 1 cell or cells not of interest, use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And _
Target.Column = 3 Then
Application.EnableEvents = False
Cells(Target.Row, 1) = Date
Application.EnableEvents = True
End If
End Sub

Ciao

 

[marleydog]

What does the enable events actually do? Will it affect other code?

 

[erik.van.geit]

What does the enable events actually do? Will it affect other code?

When you change a cell this triggers the Sheet_change.
When this event makes other changes to the sheet it will trigger again the event Sheet_change (itself!!) which is not what you want in most cases ==> this can be quite anoying

try this

Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False
Range("A1") = Range("A1") + 1
Range("B1") = Range("A1")
'Application.EnableEvents = True
End Sub

put something in a cell, doesnt't matter which one
what do you expect ?

kind regards,
Erik

 

[marleydog]

That's a great example. I can see where that would be a problem. Should that code be used for all Worksheet_change events?

thanks again!!

 

[erik.van.geit]

That depends on what the code is doing.
You can try out by setting a breakpoint on the first line with Key F9.
When the event is triggered continou with F8 and step through it. You'll see if it's gettig triggered again..

kind regards,
Erik