Macros works by itself, but crashes Excel when Called

[john316swan]

I have this worksheet change:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If (Range("O4")) = True Then
    Range("O4").Select
    Selection.ClearContents
    Call [B]DeleteConnection[/B]
    End If


    If Not Intersect(Target, Range("q71:r71")) Is Nothing Then
    Call MakeStatic
    End If
    
End Sub

The DeleteConnection works perfectly when I run it on it's own but when it is called (from the event change above) Excel crashes

Here is the DeleteConnection macro:

Sub DeleteConnection()
'
' DeleteConnection Macro
' Deletes External Data Collections and makes RET aid static
'


'
    Range("A4:K4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("J7:N7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Values").Select
    Range("T116:V122").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("T123:U130").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Connections("YR-16 Weekly Report").Delete
    Sheets("Values").Select
    Sheets("RET").Visible = True
    Sheets("RET").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets("TUG-E").Select
    Range("L4:N4").Select
End Sub

 

[Snakehips]

John,

The worksheet change code and the calls it is making are resulting in changes to the sheet hence the worksheet change event is triggered ad-infinitum and Excel disappears up its own whatsit.

Try the below and see if that sorts it.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If (Range("O4")) = True Then
    Range("O4").Select
    Selection.ClearContents
    Call DeleteConnection
    End If




    If Not Intersect(Target, Range("q71:r71")) Is Nothing Then
    Call MakeStatic
    End If
  Application.EnableEvents = True  '  Must always reset this
End Sub

Hope that helps.

 

[JoeMo]

If your called routines are changing cells (appears at quick glance they are) then you need to turn off events while they run. See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If (Range("O4")) = True Then
    Range("O4").Select
    Selection.ClearContents
    Call DeleteConnection
    End If


    If Not Intersect(Target, Range("q71:r71")) Is Nothing Then
    Call MakeStatic
    End If
    Application.EnableEvents = True
    
End Sub

 

[john316swan]

Worked perfectly! Thank you so much!