0

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?

Below is my code:

Private Sub WeldCalc_Click()

Range("C428").Select
ActiveCell.FormulaR1C1 = "10"
Range("R502").GoalSeek Goal:=0, ChangingCell:=Range("C428")
ActiveWindow.ScrollRow = 1

End Sub


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
    WeldCalc_Click
End If
    
End If 

End Sub

enter image description here

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

StructEng
  • 11
  • 1
  • Have you tried some/all suggestions from https://stackoverflow.com/q/2154699/11683? – GSerg Apr 04 '23 at 21:45
  • Yes I did but none of those seem to work – StructEng Apr 04 '23 at 22:10
  • Please post your actual code, not a picture of it. I'm assuming the issue lies with the `.GoalSeek` line? – Notus_Panda Apr 04 '23 at 22:27
  • Sorry, I am new to this website. I have just posted the actual code. My spreadsheet initially only had the the Macro "WeldCalc_Click()" and it ran without issues. When I then went to update my spreadsheet with new features, adding the Worksheet_Change function, I keep getting frequent "Code execution has been interrupted" error. If I hit continue, it completes the operation as normal. It I hit Debug, it highlights the Private Sub Worksheet_Change(ByVVal Target As Range). Then if I hit play or stepthrough line by line, it finishes the execution as normal. – StructEng Apr 04 '23 at 22:43
  • Does `WeldCalc_Click` modify the contents of the worksheet? – braX Apr 05 '23 at 01:17
  • WelcCalc_Click is a Macro that activates when you normally click on the macro button. When clicked or called in VBA, it will calculate the strength of my weld group by changing bunch of numbers until it reaches a difference in value of almost 0. – StructEng Apr 05 '23 at 14:06

1 Answers1

0

A Worksheet Change: Calling Another Procedure to Mess With the Sheet

Private Sub WeldCalc_Click()
    WeldCalc Me
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const TGT_RANGE As String = "C36,C39,C40,C41,C43,C44"
    
    On Error GoTo ClearError
    
    Dim IsReady As Boolean
    
    If Intersect(Target, Me.Range(TGT_RANGE)) Is Nothing Then Exit Sub
    If CStr(Me.Range("C34").Value) <> "DWB" Then Exit Sub
    
    Application.EnableEvents = False
    WeldCalc Me
    
ProcExit:
    On Error Resume Next
        If Not Application.EnableEvents Then Application.EnableEvents = True
    On Error GoTo 0
Exit Sub
ClearError:
    MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf _
        & Err.Description
    Resume ProcExit
End Sub

Sub WeldCalc(ByVal ws As Worksheet)
    ws.Range("C428").FormulaR1C1 = "10"
    ws.Range("R502").GoalSeek Goal:=0, ChangingCell:=ws.Range("C428")
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28