VBA Date and time stamp.

[Liverlee]

Hello All.

Can anyone help me with this VBA issue?

In column D users will add info. In column e i have a date stamp and in column f a time stamp. So if anyone amends the info in column d column e will update the date and column f the time.

However if i copy and paste info in to columns a-c the vba doesn't work.

Can anyone explain where it's going wrong or suggest a workable alternative

--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'Check if target cells are empty
If Target.Column = 4 And Cells(Target.Row, 1) = "" And Cells(Target.Row, 2) = "" Then

'disable events
Application.EnableEvents = False

'insert current date into column 5
Cells(Target.Row, 5) = Date

'insert current time into column 6
Cells(Target.Row, 6) = Time

'Enable events
Application.EnableEvents = True


End If
End Sub
-----------------

Thanks

 

[Flaiban]

A little adjustments :

Private Sub Worksheet_Change(ByVal Target As Range)

'Check if target cells are empty
If Target.Column = 4 And Cells(Target.Row, 1) = "" Or Cells(Target.Row, 2) <> "" Then

'disable events
Application.EnableEvents = False

'insert current date into column 5
Cells(Target.Row, 5) = Date

'insert current time into column 6
Cells(Target.Row, 6) = Time

'Enable events
Application.EnableEvents = True


End If
End Sub

 

[Micron]

the vba doesn't work

That means what? Doesn't run? Does but doesn't do what you expect? Did you put a break point at the start and then paste to ensure the event takes place?
AFAIK, paste should fire that event. Why don't you just put result of Now() in one cell instead of splitting into two parts?

 

[Liverlee]

Thank you Flaiban

Micron, i don't know why it didn't work, i put some dummy data in d and e and f updated as expected. However when i copied and pasted info into columns, a, b, c and then went to amend d - columns e and f didn't update and shown the new time. It remained stuck with the old time stamp.

I put the results in two parts for the purposes of conditional formatting dates but it could be done in just one cell.