How to turn off change(byval target as range) after macro run

[Jemini Jimi]

When this code runs once, I need it to stop but it keeps looping because the trigger cell is in the macro.
I read about Application.EnableEvents but I cannot get it to work.

I am trying to copy and paste values in selected cells after the file is saved.
Here are the range of cells>>> D2:E2 , D3:D4, J29:L29

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("D2:E2")) Is Nothing Then SaveAsFilenameInCell
End Sub

Macro runs

Sub SaveAsFilenameInCell()
    Dim FileName As Variant
    Dim ValCellA1 As String
    Dim Path As String
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    MsgBox "Time to SAVE this file in the JOB FOLDER", vbInformation, "Save File"
    
    ValCellA1 = ThisWorkbook.Sheets("Subcontract").Range("A1")
    
    FileName = Application.GetSaveAsFilename(Path + ValCellA1 + ".xlsm", _
        "Excel Workbook,*.xlsm", 1, "Confirm Folder")
    
    If TypeName(FileName) = "Boolean" Then
        MsgBox "No File was saved"
    Else
'copy and paste job Data and date
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
'this range is the trigger
    Range("D2:E2").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .MergeCells = True
    End With
    Range("D3:D4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("J29:L29").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("J29:L29").Select
    Selection.Merge
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .MergeCells = True
    End With
    
    ActiveWorkbook.SaveAs FileName
    MsgBox "File was Saved", vbInformation, "File Saved"
    ActiveSheet.Protect
    Application.ScreenUpdating = True
    End If
End Sub

 

[myall_blues]

Add the line

Application.EnableEvents = False

like so:

Sub SaveAsFilenameInCell()
    Dim FileName As Variant
    Dim ValCellA1 As String
    Dim Path As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    ' 
    ' Rest of code
    '
End Sub

but note that unless you switch it back on at the end (Application.EnableEvents = True) before the End Sub then all event handling will remain switched off in the current Excel session. For example your Worksheet_Change events will no longer trigger.

 

[Jemini Jimi]

Ok, I was following some other advice saying to place that in the Change(ByVal Target As Range).
Now I understand.
Thanks that works great!

 

[myall_blues]

You’re welcome. Thanks for the feedback.