PDA

View Full Version : Solved: Code to automatically enter data update date



U_Shrestha
05-16-2008, 06:16 AM
Hi all,

I have the following code to enter data update date. Lately, this code works only when I first open the workbook and type something, after sometime, the code doesn't enter date, but when I close and reopen the file and enter something, then it works again. Can someone tell me what's needs to be changed. Is it because it is conflicting with other codes? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range, rgRow As Range
On Error GoTo ExitHere
Set rg = Range("a1:b100")
If Intersect(rg, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rgRow In Intersect(Target, rg).Rows
Cells(rgRow.Row, "c") = Date
Next rgRow
ExitHere:
Application.EnableEvents = True
End Sub

matthewspatrick
05-16-2008, 06:32 AM
Do you have any other code anywhere in the VB project that toggles EnableEvents to False?

U_Shrestha
05-16-2008, 06:51 AM
I might have set enable events to false at the beginning of the code to speed up the macro, should I turn it on?

U_Shrestha
05-16-2008, 07:16 AM
I also noticed that some other macros also stops working after sometime and that for most of the macros I have used to make the macros run faster, what should I do?

Sub .... ()
Application.ScreenUpdating = False
Application.EnableEvents = False
'My macros
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

matthewspatrick
05-16-2008, 07:20 AM
I might have set enable events to false at the beginning of the code to speed up the macro, should I turn it on?

Not necessarily--sometimes it's necessary to avoid a cascade of events.

My guess is that there must be a placed where EnableEvents has been turned off and never reset.

U_Shrestha
05-16-2008, 08:12 AM
Hello Patrick,

I found one place where it wasn't turned back on and the macros are working so far. However, I will wait till end of day to mark it solved. Thanks for your help :)

matthewspatrick
05-16-2008, 08:32 AM
Glad to help :)