使用VBA删除工作表后重启Excel运行脚本时工作表复现问题求助

我用VBA宏删除指定工作表，同时删除DataStorage工作表BP列中对应工作表名称的文本，操作过程看起来正常，但关闭再重新打开Excel后，已删除的工作表又会重新出现。附上代码如下，请帮忙排查原因：

Private Sub CommandButton2_Click()
    Dim xWs As Worksheet
    Dim wb As Workbook
    Dim sheetName As String
    Dim z As Long
    Dim last As Long

    ThisWorkbook.Activate

    sheetName = Application.InputBox("Please enter the OSAT Name:", "OSAT", _
    , , , , , 2)
    last = ThisWorkbook.Worksheets("DataStorage").Cells(Rows.Count, "BP").End(xlUp).Row
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Err.Clear

    On Error Resume Next

    Set xWs = Sheets(sheetName)

    If Err <> 0 Then
        MsgBox "The OSAT '" & sheetName & "'" & "does not exist!", vbInformation, "Excel 10 Tutorial"
        Exit Sub
    Else
        xWs.Delete
        MsgBox "The OSAT '" & sheetName & "'" & "has been deleted!", vbInformation, "Excel 10 Tutorial"
        
        For z = last To 2 Step -1
            If ThisWorkbook.Worksheets("DataStorage").Cells(z, "BP").Value = sheetName Then
                Sheets("DataStorage").Cells(z, "BP").Delete
            End If
        Next z
    End If
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True
End Sub

核心问题原因 #

• 未保存工作簿：删除操作完成后，代码没有执行保存命令。Excel关闭时若未手动保存，本次会话的修改会被丢弃，重启后工作表自然恢复。
• 单元格删除逻辑错误：原代码仅删除BP列的单个单元格，而非整行。这会导致下方单元格上移，若后续有其他代码依赖BP列数据重建工作表，残留的关联逻辑会触发误创建。
• 错误处理不严谨：On Error Resume Next会掩盖删除工作表时的潜在错误（比如工作表被保护、文件只读），导致实际删除失败却弹出成功提示，造成操作完成的假象。

修复后的代码 #

Private Sub CommandButton2_Click()
    Dim xWs As Worksheet
    Dim sheetName As String
    Dim z As Long
    Dim last As Long
    Dim wsData As Worksheet
    
    ' 提前绑定DataStorage工作表，避免重复引用
    Set wsData = ThisWorkbook.Worksheets("DataStorage")
    
    sheetName = Application.InputBox("请输入OSAT名称:", "OSAT", , , , , , 2)
    ' 用户取消输入时直接退出
    If sheetName = "" Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' 改用带错误捕获的检查方式，避免掩盖问题
    On Error GoTo ErrorHandler
    Set xWs = ThisWorkbook.Sheets(sheetName)
    
    ' 删除目标工作表
    xWs.Delete
    MsgBox "OSAT '" & sheetName & "' 已删除!", vbInformation, "提示"
    
    ' 删除DataStorage中对应整行，清理关联数据
    last = wsData.Cells(Rows.Count, "BP").End(xlUp).Row
    For z = last To 2 Step -1
        If wsData.Cells(z, "BP").Value = sheetName Then
            wsData.Rows(z).Delete
        End If
    Next z
    
    ' 关键步骤：删除后立即保存工作簿
    ThisWorkbook.Save
    
Cleanup:
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Exit Sub
    
ErrorHandler:
    MsgBox "OSAT '" & sheetName & "' 不存在!", vbInformation, "提示"
    Resume Cleanup
End Sub

额外注意事项 #

• 确保工作簿处于可编辑状态（非只读），否则保存操作会失败，需手动保存或调整文件权限。
• 检查是否有其他VBA代码（如Workbook_Open事件）会根据DataStorage的BP列自动创建工作表，若有需同步调整逻辑，避免误重建已删除的工作表。

内容的提问来源于stack exchange，提问作者user19457634