使用 VBA 删除工作表会使 Excel 崩溃

Deleting Sheet with VBA crashes Excel

提问by Ole Henrik Skogstr?m

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.

当用户单击 Excel 中的图像（按钮）时，我试图删除工作表。然而，这会使 excel 崩溃并重新启动，忘记任何未保存的进度。

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.

编辑：此功能不会删除工作簿中的最后一张工作表。还有20张。我也使用Application.Calculation = xlCalculationAutomatic，因为有很多公式，我不希望 excel 在删除所有连接的工作表之前计算更改。

Any hint or answer is appreciated :)

任何提示或答案表示赞赏:)

回答by Ole Henrik Skogstr?m

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 ;)

如果有人可以添加此错误的原因，请添加此答案；)

回答by an??nim?dē

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

回答by blaow

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.

这不一致地导致崩溃，直到我在删除之前添加了“ws.Activate”行来激活每个工作表，这似乎已经解决了问题。我在许多其他情况下在工作表上执行操作时遇到过这个问题，但它通常会导致对象错误而不是完全崩溃。

回答by Kenney

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.

我发现在 Office 2013 中，您不能放置与该宏更改的单元格重叠的按钮。有趣的是，如果更改本质上是数字，则不会发生这种情况，但如果是字母数字，则当您尝试删除该选项卡时，它会炸毁 excel。事实证明，当尝试手动（通过鼠标单击）或宏尝试删除选项卡时，它会炸毁它。因此，我从这个线程中吸取的教训并将其应用于我的特定情况是永远不要在它更改的单元格上放置开发按钮（在我的情况下，它只是一个提供该宏正在执行的操作的状态的单元格）。Excel 2013 不喜欢这种情况，而 Excel 2010 根本不在乎。

回答by glh

I do believe you nare right and the only way around this is to ensure this macro is on the total plansheet. 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

回答by Sven62

You candelete 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

回答by Bob Lawson

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:

我有一个类似但不完全相同的问题。我有一个宏，它使用以下命令删除了所有图表，但尽管它运行正常，但一旦我尝试保存文件，Excel 2013 就注定会失败，并且会通过恢复到以前保存的情况来“恢复”，丢弃任何后续工作：

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:

哦，它运行良好，直到我从 Excel 2010 转移到 2013 年，同时更改为 Windows 10。厄运的命令是：

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 = Falsebefore the for loop and of course the Application.DisplayAlerts = Trueafter the Next... statement to cut out the unwanted

我还应该提到，Application.DisplayAlerts = False在 for 循环之前有一个前导，当然Application.DisplayAlerts = True在 Next... 语句之后有一个去除不需要的

are you sure you want to do this type question?

你确定要做这种类型的问题吗？

Again, thanks to your contributors for the inspiration.

再次感谢您的贡献者的灵感。

回答by Paul Melville

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.

我在名为“DeleteSheet”的隐藏工作表中创建了一个范围，以存储隐藏工作表的名称。

---

---

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

回答by dmhzx

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.

我在 Excel 2016 中遇到了一个问题，如果代码在模块中，则 Option explicit 不起作用，但如果代码在模块中，那么您“应该”能够删除按钮所在的工作表。