locked
Don't Want to Save Changes RRS feed

  • Question

  • We get this every time we run the macro and the answer is always no.  Can we add something to the code to stop it popping up?

    Image

    Sub Checklist()
    '
            Dim wbk As Workbook
         Set wbk = ActiveWorkbook
         'formatting prior to copying over
        Columns("T:T").Select
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
            "T1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("A2:AI99")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Columns("P:P").Select
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
            "P1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("A2:AI99")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
       End With
        
        Range("A2:AI99").Select
        Selection.Copy
        Workbooks.Open ("E:\Admin\ADMINISTRATIVE FOLDER\SWF Macro Templates\Checklist.xltm")
           
        ActiveSheet.Paste
        Application.CutCopyMode = False

        Sheets("Data").Visible = False
       
        Sheets("Sheet1").Select
       
            ActiveSheet.Unprotect
           
            ''This is to copy over all cell with just formulas and values
    '         Cells.Select
    '    Selection.Copy
    '    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
     '       xlNone, SkipBlanks:=False, Transpose:=False
     
     '  'Code to Delete all Other Sheets
      '     Application.DisplayAlerts = False
       '  For Each Sheet In Application.Worksheets
        '     If Sheet.Name <> "Sheet1" Then
        '         Sheet.Delete
       '      End If
       '  Next Sheet
       '  Application.DisplayAlerts = True
        Range("H1:K5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
      
        Range("M2").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
           
        Range("E62").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
           
        'Hides blank rows within the box from column B
     For k = 59 To 12 Step -1
       If Len(Cells(k, "B").Value) < 1 Then
          Cells(k, 1).EntireRow.Hidden = True
       End If
      Next k
       
        'Protects Sheet
                ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
            False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
            :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
            AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
            AllowUsingPivotTables:=True

           
             Application.CutCopyMode = False
         wbk.Close SaveChanges:=False ' or True if you want to save the workbook

           Dim FileName As String
         FileName = Environ("USERPROFILE") & "\Documents\Temp Paperwork\" _
                    & " Checklist.xlsm"
         ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:= _
                     xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
         Application.DisplayAlerts = True
        
        Range("D8").Select


    End Sub

    Monday, May 8, 2017 10:25 PM

All replies

  • Not sure which section of the code this comes up but I am assuming where you close a workbook. Anyway the following line of code should suppress alert message.

    Application.DisplayAlerts = False

    However, you should realize that alerts remain turned off until Excel is re-started unless you turn them back on again with code. Therefore, insert the above line of code immediately prior to the line that results in the alert being displayed and then immediately after that line, insert the following line to reactivate alerts.

    Application.DisplayAlerts = True


    Regards, OssieMac

    • Proposed as answer by Chenchen Li Tuesday, May 23, 2017 5:22 AM
    • Unproposed as answer by Chenchen Li Tuesday, May 23, 2017 5:23 AM
    Tuesday, May 9, 2017 4:45 AM
  • Hi DougK2016,

    Thanks for visiting our forum. 

    Then this is the forum to discuss questions and feedback for Microsoft Excel. Since your issue is about using code in Excel, we'll move your question to the MSDN forum for Excel for better response:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, May 9, 2017 6:25 AM
  • Hello,

    Have you resolved the issue? We would appreciate if you could share your solution here.

    If not, I would suggest you run the macro line by line and see in which line you would get the prompt.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 23, 2017 5:30 AM