CODEWITHSUNDEEP
excelvba
Aaron Berg
Aaron Berg

Reputation: 15

Error 440 After I add application.displayalerts = false

When I add Application.DisplayAlerts = false and Application.DisplayAlerts = True either side of sheets("Sheet2").Delete I get the 440 error.

Not sure what the issue is but it runs no problem without the application.displayalerts.

I have been googling for ages I have tried a couple things mentioned on other questions posted on here. I feel like maybe i need another line of code or something.

Code that isnt working:

    Private Sub CommandButton1_Click()

     Dim numberCopies As Long
            Dim currentRow As Long
            Dim j As Long
            Dim sht As Worksheet
            Set sht = Sheets("sheet3")
            currentRow = 2

     Do While Not IsEmpty(sht.Cells(currentRow, 1))
                numberCopies = sht.Cells(currentRow, 1)
                For j = 2 To numberCopies
                    sht.Rows(currentRow).Copy
                    sht.Rows(currentRow).Insert Shift:=xlDown
                    currentRow = currentRow + 1
                Next j
                currentRow = currentRow + 1
            Loop
            Application.CutCopyMode = False
            sht.Columns(1).Delete
        Dim Path As String
        Dim Filename1 As String
        Dim Filename2 As String

     Path = ThisWorkbook.Path & "\"
    Filename1 = Range("B1")  
    Filename2 = Range("D1")




        Application.DisplayAlerts = False
        Sheets("Sheet2").Delete
        Sheets("Sheet1").Delete
        Application.DisplayAlerts = True



        ActiveWorkbook.SaveAs Filename:=Path & Filename1 & "-" & Filename2 & ".csv", FileFormat:=xlCSV

       MsgBox "This usage file as been saved in the same folder as the Usage Upload Creator, it is saved as " & ActiveWorkbook.Name & " This workbook will now close and you can upload your usage file on CPQ. Thank You."
        ActiveWorkbook.Close False
        End Sub

I just dont want the "are you sure you want to delete the sheet" alert popping up.

Upvotes: 1

Views: 139

Answers (1)

erazorv4
erazorv4

Reputation: 374

Worked out in the comments what the problem was, The problem is that you cannot have the ActiveX button on the sheet that you're trying to delete. You can supress the error and alerts by using this:

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

Upvotes: 0

Related Questions