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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
L

Legacy 456129

Guest
Got those at times and the only solution i've found to be working is closing the Excel session altogether and reopen. Usually is does not return until long hours and many runs. One thing I'm doing is making sure that any big arrays get Redim ArrayX(0) or (1) at the end of procedures and Erase ArrayX, to use an arbitrary name. Frees up memory. Also got DoEvents in many places so all systems are caught up before moving on to the next step.

Here's one discussion on the problem. Automation Error in Excel VBA Macro
 

albertc30

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

I have removed this line and it's working now with no issues.

'Application.DisplayAlerts = True 'This is causing the issue...

Could there be underlying issues cause by the lack of this removed line?

Thanks.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Out of curiousity, will you have the same error if you pass DisplayAlerts to the top of the routine?

And more curiosity - do you need to call Beep? Doesn't it already beep natively as/when the msgbox prompts?
 

albertc30

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

ADVERTISEMENT

Out of curiousity, will you have the same error if you pass DisplayAlerts to the top of the routine? 'Haven't tried it yet.

And more curiosity - do you need to call Beep? Doesn't it already beep natively as/when the msgbox prompts?
The beep is natively only on ! marks it seems, hence I am using it.

But I have now added another line "ActiveWorkbook.save 'This is not doing what's supose to do.." and it's not saving the excel file at all...
 
L

Legacy 456129

Guest
Funny, I use PlaySound at times to play a short tune from the Windows library to alert on end of a long procedure or when an error trap routine was called. At times it stops on Playsound and comes up with 'cannot execute in break mode', but then when hitting enter, it just finishes without further issue or the script crashing out. The 'cannot execute in break mode' has pestered me for years with the Continue or End options only when setting break points. After going on the web to pull data with a long script, no more debug/stepping is possible from thereon, so am resorting to fetch the data, then comment out the fetch routine and then rerun to debug everything after. /OT
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

But I have now added another line "ActiveWorkbook.save 'This is not doing what's supose to do.." and it's not saving the excel file at all...
Can you share your modified code?
 

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
Can you share your modified code?
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
                
                Application.DisplayAlerts = False 'This is causing the issue...
                
                ActiveSheet.Delete
                
               'Application.DisplayAlerts = True 'This is causing the issue...
               
               Sheets("Create").Select
               
               ActiveWorkbook.save 'This is not doing what's supose to do...
               
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,633
Messages
5,626,009
Members
416,152
Latest member
TedX

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