Automactically Updating Date Runs SLOW

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have successfully automatically updated my "date" cell with the current date when a change is made to a cell on the specified worksheet, however it runs really SLOW. Is there any way to speed it up? Such as getting it to check for changes only when the user exits/closes the sheet and updating the date then or perhaps make it check only the cell the user was just in. Below is the code I am using now.

Private Sub Worksheet_Change(ByVal Target As Range
Range(Cells(1, 6), Cells(1, 6)) = No
End Su

Any help is appreciated! Thanks Cyn
 
This may run marginally faster:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "F1" Then
Application.EnableEvents = False
Target.Value = Date
Application.EnableEvents = True
End Sub

The reason your code is so slow is that when you put the value Now into
F1, it causes the Worksheet_Change event to be fired...which causes your
Worksheet_Change() event macro to put the value Now in F1...which causes
the Worksheet_Change event to be fired...which causes...

Eventually, the routines run out of stack space, after which they start
to unwind.

You'd find if you changed your macro to

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range(Cells(1, 6), Cells(1, 6)) = Now
Application.EnableEvents = True
End Sub

that you'd never miss the milliseconds of your life that were wasted by
not checking which cell was changed.
 
Back
Top