worksheet_change event stops working

Hello. There was a thread in 2013 that discussed my issue, but it ended in an argument over rules. So I'll ask it here:

My worksheet change event macros stop working what seems to be randomly. If I close and reopen the workbook, they start working again. This does happen after a vba compiler error is triggered (I do have an on error control in the code). Please let me know what causes this or if you need more info. I can attach the code - which is only a few lines. Thanks!!

Originally Posted by kutach770

Hello. There was a thread in 2013 that discussed my issue, but it ended in an argument over rules. So I'll ask it here:

My worksheet change event macros stop working what seems to be randomly. If I close and reopen the workbook, they start working again. This does happen after a vba compiler error is triggered (I do have an on error control in the code). Please let me know what causes this or if you need more info. I can attach the code - which is only a few lines. Thanks!!

My heading is:

Sub worksheet_change (byval target as range)

BTW - I'm not sure about all the rules, but if someone figures out their own question (as the person did in the post I referred to above), they really should share their solution.

It worked! I deleted a dim statement to cause an error. After fixing it the procedure continued to work. I so appreciate it. Is there a thank you button or something I can do?

For what it's worth, here's the code:

Private Sub worksheet_change(ByVal target As Excel.Range)

On Error GoTo ErrorHandler


Dim DRange As Range
Dim PRange As Range


Set DRange = Range("Dool")
Set PRange = Range("Pool")

Application.EnableEvents = False

If Not Intersect(target, DRange) Is Nothing Then

target.Offset(0, 1) = target / target.Offset(0, -1)
If (target.Offset(0, 2) > 0) Or (Me.Range("errdollar") > 1) Then
MsgBox "Your proposed amount is below minimums", vbCritical

End If


ElseIf Not Intersect(target, PRange) Is Nothing Then

target.Offset(0, -1) = target * target.Offset(0, -2)

End If



Application.EnableEvents = True

ErrorHandler:

Resume Next
Application.EnableEvents = True

End Sub

I also had this issue of Worksheet_Change randomly not working. I finally found the problem. It was the ID-10-T error. I was debugging the code, and the compiler was still stopped at a breakpoint. I made a change in the code, and switched back to the worksheet to change a cell and fire the event again. And - Voila! - Nothing! But of course it did not fire because the debugger was still running from before. So I stopped the debugger and the event started working again.

I also had this issue of Worksheet_Change not working, but it was random. I was starting to suspect the compiled code had become corrupt, but I eventually found the problem. It was a silly mistake on my part.

I was debugging the code, and left the debugger running. I changed the code and switched back to the worksheet to make the event fire again. And - Voila! - Nothing! But of course it did not fire because the event was still stopped in the debugger from before. So I stopped the debugger and it started working again.