Excel VBA: "Code execution has been interrupted" for Worksheet_Change(ByVal Target As Range)

[StructEng1]

Hello,

I wrote a vba code where if certain cells changes in value that excel will then run an existing macro. However, I keep getting an "Code execution has been interrupted" error almost 80% of the time that it runs. When I hit debug and press continue, everything works normally so I know there is nothing wrong with my code. Can someone please help me with this issue? I also wrote many different versions of this code and I get the same error all of which points to Worksheet_Change(ByVal Target As Range) yet runs fine when you hit continue.

I even tried the phantom breakpoint method where you press ctrl pausebreak twice but it does not work.

 

[Jimmypop]

Hi there

The error "Code execution has been interrupted" is typically caused by a runtime error in your VBA code. While hitting "Debug" and then "Continue" may allow the code to run without error, it does not necessarily mean that there is nothing wrong with your code.

One possible cause of this error could be that your code is triggering the Worksheet_Change event recursively, which can cause Excel to become unresponsive and eventually result in an error. To prevent this, you can disable events before making changes to the worksheet, and then re-enable events afterwards. You can use the Application.EnableEvents property to do this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C34") = "DWB" Then
        If Target.Address = "$C$36" Or Target.Address = "$C$39" Or Target.Address = "$C$40" Or Target.Address = "$C$41" Or Target.Address = "$C$43" Or Target.Address = "$C$44" Then
            Application.EnableEvents = False 'disable events
            WeldCalc_Click
            Application.EnableEvents = True 're-enable events
        End If
    End If
End Sub

Another possible cause of the error could be related to the WeldCalc_Click macro. You can try adding error handling code to this macro to help identify any potential issues:

Private Sub WeldCalc_Click()
    On Error GoTo ErrorHandler 'add error handling
    Range("C428").Value = 10 'use Value instead of FormalaR1C1
    Range("R502").GoalSeek Goal:=0, ChangingCell:=Range("C428")
    ActiveWindow.ScrollRow = 1
    Exit Sub 'exit the sub if there are no errors
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description 'display error message
End Sub

By adding error handling to your code, you can get more information about the error that is causing the "Code execution has been interrupted" message to appear.

 

[StructEng1]

Thank you for your feedback.
I tried to implement your suggestions and unfortunately it still gives me the same error. I don't know why, I get the same debug error when I change some cells that are not even mentioned in the Worksheet_Change(ByVal Target As Range) code..... and that debug error once again points to Worksheet_Change.....I am not sure why it is doing this.