Deleting Sheet with VBA crashes Excel

Question

I am trying to delete a worksheet when the user click's on an image (button) in Excel. However this makes excel crash and restart, forgetting any unsaved progress.

This is my sub:

Sub DeletePlan()

Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Dim SheetNamesToCopy As String

SheetNamesToCopy = ActiveSheet.Name



' Check what addon sheets exists for the media, then add existing ones to string
If CheckSheet("periodeplan", True) = True Then
    ThisWorkbook.SheetS(SheetNamesToCopy & " - periodeplan").Delete
End If

If CheckSheet("ukesplan", True) = True Then
    ThisWorkbook.SheetS(SheetNamesToCopy & " - ukesplan").Delete
End If

If CheckSheet("Input", True) = True Then
    ThisWorkbook.SheetS(SheetNamesToCopy & " - Input").Delete
End If

SheetS("Totalplan").Select
ThisWorkbook.SheetS(SheetNamesToCopy).Delete

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

The application crashes most of the time. But not always... Any ideas what might be wrong? (I have tested and confirmed that the delete function causes the crash, but its not always the same sheet).

Edit: This function is not deleting the last sheet in the workbook. There are 20 more sheets. Also i use Application.Calculation = xlCalculationAutomatic, because there are allot of formulas, and i do not want excel to calculate changes before all is connected sheets are deleted.

Any hint or answer is appreciated :)

Answer 1

Resurrecting this thread because I had the same issue and want to share the solution.

I had a very simple sub to delete worksheets:

Sub deletetraindoc()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Sheets

    'This if statement looks for any worksheet that contains the term "Form" 
    'Any worksheet that contains that string will be deleted
    If InStr(ws.Name, "Form") > 0 Then
            Application.DisplayAlerts = False 'Deactivates the standard deletion confirmation
            ws.Activate
            ws.Delete 'Deletes the worksheet
            Application.DisplayAlerts = True 'Reactivates display alerts
    End If
Next
Application.ScreenUpdating = True

End Sub

This inconsistently caused crashing until I added the line "ws.Activate" to activate each worksheet before deleting, which seems to have resolved the issue. I've run into this problem in many other situations performing actions on worksheets, but it usually would result in an object error instead of a complete crash.

Answer 2

How about moving the button code to a module?

I have had an issue with that in Excel 2016 whereby Option explicit didn't work if the code was in a module, but if the code is in a module, then you 'should' be able to delete the sheet where the button was.

Answer 3

I wanted a button that would delete a sheet, as the workbook was protected and could 'export' results but couldn't delete unwanted results.

My simple workaround was to have the macro hide the sheet, but then to delete the last hidden sheet, so the files dont become huge with dozens of hidden sheets.

I created a range in a hidden sheet called "DeleteSheet", to store the name of the hidden sheet.

---

Sub Delete_Sheet()

ActiveWorkbook.Unprotect Password:="Patrick2017"

ActiveSheet.Unprotect Password:="Patrick2017"

On Error Resume Next

' (In event there is no hidden sheet or the sheet is already deleted, resume next)

'The below finds the name of the previously hidden sheet to delete, and stores it.

Dim DeleteSheet As String
DeleteSheet = Range("DeleteSheet")

'The below is to avoid the main sheet being deleted

If ActiveSheet.Name = "POAL Calculator" Then     
Exit Sub                                        
End If

' The below stores the current sheets name before hiding, for deleting next time the 
' macro is run

Range("DeleteSheet") = ActiveSheet.Name          
ActiveWindow.SelectedSheets.Visible = False 

' The below deletes the sheet previously hidden

Application.DisplayAlerts = False 
Sheets(DeleteSheet).Delete


ActiveWorkbook.Protect Password:="Patrick2017"
Application.DisplayAlerts = True

End Sub

Answer 4

I had a similar, but not identical problem. I had a macro that deleted all the chart sheets with the following command, but although it operated correctly, Excel 2013 was doomed to fail as soon as I tried to save the file, and would "recover" by reverting to the previously saved situation, throwing away any subsequent work:

Oh, and it worked fine until I moved from, I think it was, Excel 2010 to 2013, changing to Windows 10 at the same time. The command of doom was:

ThisWorkbook.Charts.Delete

Thanks to some inspiration from the answers above, I first inserted a save before the deletion action and then changed the delete as follows (it turned out the saves were not after all needed but I like to have them there, even if I comment them out):

Dim graphSheet As Chart
ActiveWorkbook.Save
For Each graphSheet in this Workbook.Charts
graphSheet.Delete
ActiveWorkbook.Save
Next graphSheet

I should also mention that there is a preceeding Application.DisplayAlerts = False before the for loop and of course the Application.DisplayAlerts = True after the Next... statement to cut out the unwanted

are you sure you want to do this type question?

Again, thanks to your contributors for the inspiration.

Answer 5

I just ran into this problem myself! I'm going to defer to more experienced designers on a way to refine this technique, but as a general concept, I do have a working solution:

If you allow the macro to run it's course and then delete the sheet, it doesn't crash. Something like this:

Sub Delete_This_Sheet()

    Application.OnTime Now + TimeValue("00:00:02"), "Watergate"
    Sheets("Sheet with a death sentence").Visible = False
    
End Sub

Sub Watergate() 'To make things go away

    Application.DisplayAlerts = False
    Sheets("Sheet with a death sentence").Delete
    Application.DisplayAlerts = True

End Sub

Answer 6

I found that in Office 2013, you cannot place a button that overlaps a cell that that macro changes. Interesting enough, it doesn't occur if the change is numeric in nature, but if it is alphanumeric, it blows up excel when you attempt to delete that tab. Turns out, it blows it up when attempting to delete the tab manually (by mouse click) or by the macro attempting to do it. THUS, my lesson learned from this thread and applying it to my specific situation is to never place a development button over the cell it changes (in my case, it was simply a cell that gives the status of what that macro was doing). Excel 2013 does not like that situation while Excel 2010 simply didn't care.

Answer 7

You can delete the active sheet from a button (or image) on the active sheet. You just have to work around it.

ActiveSheet.Move before:=Worksheets(1)
Worksheets(2).Activate
Worksheets(1).Delete

Answer 8

I do believe you nare right and the only way around this is to ensure this macro is on the total plan sheet. Also you're doing a few unnecessary steps and the select a sheet should be to activate and select a cell.

Sub DeletePlan()    
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Dim SheetNamesToCopy As String

SheetNamesToCopy = ActiveSheet.Name

'dont delete total plan
If sheetnames = "Totalplan" then exit sub      

SheetS("Totalplan").Activate
Activesheet.Cells(1,1).select  

'Turn off errors if sheet doesn't exist 
On error resume next 
ThisWorkbook.SheetS(SheetNamesToCopy & " - periodeplan").Delete
ThisWorkbook.SheetS(SheetNamesToCopy & " - ukesplan").Delete
ThisWorkbook.SheetS(SheetNamesToCopy & " - Input").Delete
ThisWorkbook.SheetS(SheetNamesToCopy).Delete
On error goto 0

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

Answer 9

The error occurs when the button that initiates the macro is located on one of the sheets that are to be deleted.

So the answer is: Do not create a button (or image linked to a macro) that deletes the sheet it is on.

If anybody can add to this answer with a reason for this error, please do so ;)