Worksheet Change Event macro fails intermittently

[Partho]

My worksheet change event macro is like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False 'prevents change event indefinite loop

<Loop macro resets formulas in 20 rows in one column using counter n>

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

The macro works sometimes but fails intermittently. This happens even if I
comment out the EnableEvents = False/True code.
Any ideas on why it could be failing and/or how I could investigate would be
appreciated, thanks.

 

[Partho]

When I say the macro (in my original post) fails I mean the macro does not
fire (sometimes) when there is a change done to the worksheet.

 

[Dave Peterson]

Do you have any "exit sub" lines in the code you didn't share?

How about "End" or "End Sub" in that code?

You may want to post the entire code.

 

[Partho]

The full code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False 'prevents change event indefinite loop
'
'Loop through net present value formulas and reset the formulas if any
average age has changed
n = 0
Do Until n = 20 'there are 20 rows
With Range("NPV1").Offset(n, 0)
.FormulaR1C1 = "=NPV(R" & .Row & "C" & .Column - 1 & ",R" & .Row
& "C" & Range("FVYr1").Column & ":R" _
& .Row & "C" & Range("FVYr1").Column +
Range("Yrsto65").Offset(n, 0) - 1 & ")"

End With
'Increment counter by 1 to reset PV formula of next cell down
n = n + 1
'
Loop
'
Application.EnableEvents = True

'Copy paste value in NominalAL1 cell so as to activate change event in
AL worksheet which will reset
'PV formulas in AL sheet
With Sheets("AL Prov").Range("NominalAL1")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Sheets("LSL Prov").Activate
Application.ScreenUpdating = True
End Sub

The bottom part of the code basically does a copy paste value in a cell in
another worksheet so that a change event is triggered in that other worksheet
as well. There is a similar worksheet change event in the other worksheet. I
did this so that the formulas in both the worksheets are in sync.

 

[Dave Peterson]

I don't see anything in your code that would turn that .enableevents off.

The next thing I'd look at is this section:

With Sheets("AL Prov").Range("NominalAL1")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Sheets("LSL Prov").Activate
Application.ScreenUpdating = True

Are there any events in the "AL Prov" or "LSL Prov" modules that toggle the
setting and forget to turn it on?

I'd fix that code (if there is). But you could try moving the line:

application.enableevents = true
to the bottom of the routine. Right before the End Sub.

And one more question...

Does the problem occur when you're really using the workbook or when you're
testing? I know that when I'm testing, I'll often (too often!) stop the code
with that .enableevents = false. So I have to type:

application.enableevents = true

into the VBE's immediate window so that I can continue testing.

The full code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False 'prevents change event indefinite loop
'
'Loop through net present value formulas and reset the formulas if any
average age has changed
n = 0
Do Until n = 20 'there are 20 rows
With Range("NPV1").Offset(n, 0)
.FormulaR1C1 = "=NPV(R" & .Row & "C" & .Column - 1 & ",R" & .Row
& "C" & Range("FVYr1").Column & ":R" _
& .Row & "C" & Range("FVYr1").Column +
Range("Yrsto65").Offset(n, 0) - 1 & ")"

End With
'Increment counter by 1 to reset PV formula of next cell down
n = n + 1
'
Loop
'
Application.EnableEvents = True

'Copy paste value in NominalAL1 cell so as to activate change event in
AL worksheet which will reset
'PV formulas in AL sheet
With Sheets("AL Prov").Range("NominalAL1")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Sheets("LSL Prov").Activate
Application.ScreenUpdating = True
End Sub

The bottom part of the code basically does a copy paste value in a cell in
another worksheet so that a change event is triggered in that other worksheet
as well. There is a similar worksheet change event in the other worksheet. I
did this so that the formulas in both the worksheets are in sync.

 

[Partho]

Dave,

Your tip about using the Immediate Window to turn EnableEvents on/off solved
the problem! Thanks.

Partho

 

[Dave Peterson]

I'm not sure if it solved the problem. It did fix it at least temporarily. But
it doesn't explain why that setting is not getting changed back.

Dave,

Your tip about using the Immediate Window to turn EnableEvents on/off solved
the problem! Thanks.

Partho