Delete sheet with vb without propmpt... still coming up with error but all actions are executed.

[albertc30]

Hi all again.

Code bellow runs okay and executes all instructions.

However, there's still an error popping up.

Private Sub CBProfoInvoice_Click()

    Beep
    If MsgBox("You have selected a proforma invoice document." & vbNewLine & "This will generate your next invoice?", vbQuestion + vbYesNo, "Proforma to Invoice...") = vbNo Then

        Exit Sub
        
    End If

             'save invoice pdf code below
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "D:\Software\Invoices\INV" & Range("F4").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False

            Dim Data(1 To 5) As Variant
            Dim DstRng As Range
            Dim RngEnd As Range
   
            Set DstRng = Worksheets("Invoices").Range("A1:E1")
            Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
            Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 5))
       
            With ActiveSheet
                Data(1) = .Range("F4")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
                Data(5) = .Range("F3") 'Proforma Number THIS IS NOT BEING COPIED ACROSS!!!
            End With
            
            DstRng = Data
               
               CBGenDocument.Enabled = False
               CBProfoInvoice.Enabled = False
               
               Application.DisplayAlerts = False 'This is causing the issue...
               
               'ActiveWindow.SelectedSheets.Visible = False
               'ActiveWindow.SelectedSheets.Delete
                
                ActiveSheet.Delete
                
               Application.DisplayAlerts = True 'This is causing the issue...
               
               Sheets("Create").Select
               
End Sub

What might be causing this?

Many thanks.

 

[albertc30]

Can you switch on DisplayAlerts before the Save and see if you get any prompts?

I have but still had the error although the code runs all the way.

The save part of the code still doesn't work.

 

[Jon von der Heyden]

I'm clutching at straws here because I have no idea why you would get an automation error, but have you tried stripping some bits out, such as:

CBGenDocument.Enabled = False
CBProfoInvoice.Enabled = False

 

[Legacy 456129]

A code snip from a workbook that does a big data copying routine to a new workbook, then names it and saves it.

55          DoEvents
56          Sleep 1500
60          .CalculateBeforeSave = False
61          .DisplayAlerts = False 'add 05.05.2017
62          .CalculateBeforeSave = False
63          .EnableEvents = True
Rem determine fileformat of how open workbook was last saved
Select Case ThisWorkbook.FileFormat '18.01.2020 add
   Case xlOpenXMLWorkbookMacroEnabled: FileFrmt = 52 'xlsm with macros
   Case xlExcel12: FileFrmt = 50 'xlsb binary with macros
   Case xlOpenXMLWorkbook: FileFrmt = 51 'xlsx 'No Macros!
   Case xlOpenXMLStrictWorkbook: FileFrmt = 61 'xlsx No Macros!
End Select '18.01.2020 add
65          With wb
70              .SaveAs _
                        Filename:=.Name, _
                        FileFormat:=FileFrmt, _
                        CreateBackup:=True, _
                        ConflictResolution:=xlLocalSessionChanges, _
                        AccessMode:=1, _
                        AddToMru:=True, _
                        Local:=False  'save as xlsb = 50 'https://powerspreadsheets.com/vba-save-workbook/
76          state = .Saved 'add 05.05.2017
75          End With

77    Select Case state 'chg 05.05.2017 from If/Then
      Case state
78        PlaySound Environ("WINDIR") & "\media\Windows Notify System Generic.wav", SND_ASYNC, 0
79        savemsg = "[" & wbname & "] saved successfully @ " & Now()
80        .StatusBar = savemsg
81        Sleep 2000
82    Case Else
83        PlaySound Environ("WINDIR") & "\media\Windows Hardware Fail.wav", SND_ASYNC, 0
84        savemsg = "The workbook save failed! The time this happened was " & Now()
85    End Select
90          Range("WhenRun") = "Save"
95          .DisplayAlerts = True 'add 05.05.2017
100         .CalculateBeforeSave = True
105         .Run "RecordWatch", "SaveFull", "End" 'Enabled 19.06.2017
106          Range("RibbonTbl[BaseScreentip]").Calculate
107         .Run "GetScreenTipUpdate", 1, "WhenSave", "", "SaveMyGrid1" 'chg 05.05.2017 to update the RibbonTbl screentip
110         .ScreenUpdating = True
120     End With

Never a hickup.

 

[albertc30]

I'm clutching at straws here because I have no idea why you would get an automation error, but have you tried stripping some bits out, such as:

CBGenDocument.Enabled = False
CBProfoInvoice.Enabled = False

Makes sense to remove that given I'm deleting that sheet.

Did that but still had the error.

The save worksheet still didn't work as well.

 

[RoryA]

I'd assume the problem is that you are deleting the sheet containing the code that is running. I'd suggest you move the code to a normal module and then use Form buttons with the macros assigned to them.

 

[albertc30]

I'd assume the problem is that you are deleting the sheet containing the code that is running. I'd suggest you move the code to a normal module and then use Form buttons with the macros assigned to them.

Went about it a different way. But you gave me the idea. Thank you.

Private Sub CBProfoInvoice_Click()

     Call Pro2Invoice
              
End Sub

 

[Legacy 456129]

Spend today some time on error catching. Interesting codes to use after a "On error resume next" and the code breaking line are

Debug.Print err.Number & " " & err.Description

This logs the error in the immediate window without needing to transcribe while working on problem resolution. After that err.Clear so the next error can be caught.

A For Each routine was stumbling on a name off an arrayed input list and found the way around with high fault tolerance in A = Switch(condition, returninfo, condition, returninfo,......, fake=0, onesizefitsall).

Switch returns a -1 so you can code around but the 'Fake = 0' is always True even if you dont Dim the variable with Option Explicit). From there it's easy to write what to do including Goto Top (in the code with a Top: to resume from the top, some some code breaking code to not end in an endless loop)

Automation error can be so many things, had one just one time today, because on execution and some manual switching landed me on the blank workbook to which I was writing the data backup to. The solution to not be caught out again is e.g. Dim wbkOrigSht as worksheet, Dim wbkOrigin as Wokbook and Set the sheet in the source INCLUDING workbook, so Set wbkOrigSht = wbkOrigin.Worksheets("sheetname") sets the script ready for I'm at wbkOrigSht ot wbkDestSht. Similarly, use Dim wbkDestin as Workbook, wbkDestSht as Worksheet with a Set to ensure the backup goes to the backup WB and not landing some place at the coordinates in the Origin WB.

My source workbook tab modules have code that prevent deletion and renaming and are mostly write protected but for some unlocked zones, so any attempt even with DisplayAlert = False would fail them to be deleted should somehow an Activate/Select land in the sourcebook.

Just some loose rant.