使用VBA删除工作表会使Excel崩溃 [英] Deleting Sheet with VBA crashes Excel

问题描述

当用户单击Excel中的图像(按钮)时，我正在尝试删除工作表.但是，这会使excel崩溃并重新启动，从而忘记了所有未保存的进度.

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.

这是我的下属:

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

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

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

推荐答案

当启动宏的按钮位于要删除的一张纸上时，发生错误.

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

所以答案是:不要创建一个按钮(或链接到宏的图像)来删除其上的工作表.

如果有人可以为此错误添加答案，请这样做;)

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

这篇关于使用VBA删除工作表会使Excel崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！