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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
Hi all again.

Code bellow runs okay and executes all instructions.

However, there's still an error popping up.

VBA Code:
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.
 

Attachments

  • Screenshot_2.png
    Screenshot_2.png
    57.7 KB · Views: 12

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
CBGenDocument.Enabled = False
CBProfoInvoice.Enabled = False
 
L

Legacy 456129

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

VBA Code:
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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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:
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,438
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
Private Sub CBProfoInvoice_Click()

     Call Pro2Invoice
              
End Sub
 
L

Legacy 456129

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

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,185
Messages
5,623,250
Members
415,957
Latest member
Newguy1924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top