Time Stamp Question

[Ganther]

I have gone through post after post and can not for the life of me get the time stamp to work.

If cell C5 on sheet2 is changed manully then Cell F5 on sheet2 shows the date it was changed simple F5 will always change to todays date when C5 is manually changed. Below is the code I am using and can not get it to work. I have also tried simpler code I have found on this forum with out getting it to work either. Please help it is driving me nuts.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C5"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[bertie]

Put the value in first, then format the cell.

With .Offset(0, 1)
   .Value = Now
   .NumberFormat = "dd mmm yyyy hh:mm:ss"
End With

ps, for output to column F your offset will need to be (0,3)

 

[AlphaFrog]

In addition to bertie's comments, your code has to be located in the sheet's module as well.

• Right-click on the sheet tab and select View Code from the pop-up menu.
• Then paste the code in the VBA edit window.

Also, in your code you disable and later re-enable events. That's good. But if you had at some point an error in your code when you were writing it. You may have left your application with the events disabled. You may have to re-enable them to get things going again.

 

[Smitty]

Welcome to the Board!

The code works fine, but change Offset(0,1) to Offset(,3) to get to F from C.

What module did you put the code in? It should be in the Sheet2 module, not a general module.

HTH,

 

[Norie]

Ganther

You might have accidentally not re-enabled events at some point.

If you goto the Immediate Window (CTRL+G) enter this and press enter.

Application.EnableEvents = True

That should 'turn' events back on, if it doesn't fix the problem then post back.

 

[Ganther]

Thanks for the help and sorry for the slow reply, that code worked and I did have to type the Application.EnableEvents = True to get it to work.. Smitty I put the code on the sheet that I want it to be on.

Thanks again for the help

 

[Norie]

Ganther

Good to see you've got it working again.

One thing you might want to do that might help avoid a similar problem would be to move Application.EnableEvents = True.

Currently it's sitting in the middle of a bunch of logical statements so it might get skipped by mistake.

Put it as the very last line of code, just before End Sub.