I don't want NOW() to always be NOW!

  • Thread starter Thread starter TraciAnnNeedsHelp
  • Start date Start date
T

TraciAnnNeedsHelp

I use =IF(ISBLANK(C26:K26),"",NOW()) to show the date for when information is
entered into adjacent cells. Once the date is provided, I don't want it to
change. As is, the date changes each day.

Thanks for your help!
TraciAnn
 
TraciAnn,

You need to use the change event to write the date in the date record cell:

For example, copy this code, right-click the sheet tab, then select "View Code" and paste the code
into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C:K")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Cells(Target.Row, "L")
.Value = Now
.NumberFormat = "mm/dd/yy hh:mm:ss"
End With
Application.EnableEvents = True
End Sub

The code will enter the date and time in column L of the row where any change to a single cell
within C:K occurs.

HTH,
Bernie
MS Excel MVP
 
WOW!!!

That is new to me. I did it, but there is still a problem. I want the date
that the information is originally put into the cells. I don't want the date
to change everytime a change is made to one of the cells. Also, it doesn't
have to be a range, it could just be a single cell.

Thanks!
TraciAnn
 
TraciAnn,

For the code to work, the change must be made to a single cell. If your workflow is such that you
might be making changes to multiple cells, then use the second version, below the first.

'This version doesn't overwrite the original date, but only records when a single cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Cells(Target.Row, "L").Value <> "" Then Exit Sub
If Intersect(Target, Range("C:K")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Cells(Target.Row, "L")
.Value = Now
.NumberFormat = "mm/dd/yy hh:mm:ss"
End With
Application.EnableEvents = True
End Sub

'This version doesn't overwrite the original date, and works on multiple cells:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myC As Range
Application.EnableEvents = False
For Each myC In Target
If Cells(myC.Row, "L").Value <> "" Then GoTo DoNext
If Intersect(myC, Range("C:K")) Is Nothing Then GoTo DoNext
With Cells(myC.Row, "L")
.Value = Now
.NumberFormat = "mm/dd/yy hh:mm:ss"
End With
DoNext:
Next myC
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP
 
Thank you for all your help.

To make sure we are on the same page, the column in which I want the date is
B.
I changed your code where "L" was referenced to "B". I added the first code
to the sheet's code.

Do I keep the current formula in column B? (=IF(ISBLANK(C26:K26),"",NOW())

Will the new code you provided me ONLY change the date in Column B the first
time the contents of another cell in the row changes?

TraciAnn
 
Column B should start completely blank, and will be filled in the first time a cell in C:K is used.
 
TraciAnn,

Make sure that your events are enabled. Run this macro to re-enable events:

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub

Also, did you replace the L with B in both places in the code?

HTH,
Bernie
MS Excel MVP
 
That Did It!!!

Thanks Bernie!
TraciAnn

Bernie Deitrick said:
TraciAnn,

Make sure that your events are enabled. Run this macro to re-enable events:

Sub ReEnableEvents()
Application.EnableEvents = True
End Sub

Also, did you replace the L with B in both places in the code?

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Congrats! And thanks for letting me know that you got it to work.


Bernie
MS Excel MVP
 
Back
Top