如何通过VBA禁用手动删除工作表时的弹窗提示？

Great question! You're right that Application.DisplayAlerts = False only works for deletions initiated via VBA code, not manual right-click deletions. Let's cover two practical solutions to handle this, depending on your needs:

方案1：替换右键菜单（简单、无API依赖） #

This method replaces the default "Delete" command in the worksheet right-click menu with a custom VBA-powered version, letting us skip the confirmation prompt entirely.

Steps: #

1. Open your workbook and press Alt+F11 to launch the VBA Editor.
2. Paste this code into the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    ' Remove the default delete command (ignore errors if it's already gone)
    On Error Resume Next
    Application.CommandBars("Ply").Controls("删除").Delete
    On Error GoTo 0
    
    ' Add a custom delete command that matches the default's appearance
    With Application.CommandBars("Ply").Controls.Add(Type:=msoControlButton)
        .Caption = "删除"
        .OnAction = "DeleteWorksheetWithoutAlert"
        .FaceId = 108 ' Use the same icon as the default delete command
        .BeginGroup = True ' Keep the same menu grouping as original
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Restore the default right-click menu when closing the workbook
    On Error Resume Next
    Application.CommandBars("Ply").Controls("删除").Delete
    Application.CommandBars("Ply").Reset
    On Error GoTo 0
End Sub

3. Insert a new Standard Module (right-click the Project Explorer > Insert > Module) and paste this code:

Option Explicit

Public Sub DeleteWorksheetWithoutAlert()
    Dim targetSheet As Worksheet
    Set targetSheet = ActiveSheet
    
    ' Prevent deleting the last worksheet (Excel doesn't allow this)
    If ThisWorkbook.Worksheets.Count = 1 Then
        MsgBox "无法删除工作簿中的最后一个工作表！", vbExclamation, "提示"
        Exit Sub
    End If
    
    ' Perform deletion without confirmation
    Application.DisplayAlerts = False
    targetSheet.Delete
    Application.DisplayAlerts = True
End Sub

Pros: #

• No Windows API dependencies, works seamlessly on 32/64-bit Excel
• Simple logic, easy to modify or troubleshoot
• Preserves the familiar right-click workflow for users

Cons: #

• Only covers right-click deletions; users who use keyboard shortcuts (like Alt+E+L) or the ribbon's delete button will still see the prompt

方案2：用Windows API拦截提示框（全面覆盖所有手动删除场景） #

If you need to block the confirmation prompt for all manual deletion methods (right-click, shortcuts, ribbon buttons), use a Windows API hook to automatically confirm Excel's delete dialog.

Steps: #

1. Open the VBA Editor and insert a new Standard Module, then paste this code:

Option Explicit

' Windows API declarations (compatible with 32/64-bit Excel)
Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExW" ( _
    ByVal idHook As Long, _
    ByVal lpfn As LongPtr, _
    ByVal hmod As LongPtr, _
    ByVal dwThreadId As Long) As LongPtr

Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" ( _
    ByVal hhk As LongPtr) As Long

Private Declare PtrSafe Function CallNextHookEx Lib "user32" ( _
    ByVal hhk As LongPtr, _
    ByVal nCode As Long, _
    ByVal wParam As LongPtr, _
    lParam As Any) As LongPtr

Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameW" ( _
    ByVal hwnd As LongPtr, _
    ByVal lpClassName As String, _
    ByVal nMaxCount As Long) As Long

Private Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthW" ( _
    ByVal hwnd As LongPtr) As Long

Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextW" ( _
    ByVal hwnd As LongPtr, _
    ByVal lpString As String, _
    ByVal cch As Long) As Long

Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExW" ( _
    ByVal hWndParent As LongPtr, _
    ByVal hWndChildAfter As LongPtr, _
    ByVal lpszClass As String, _
    ByVal lpszWindow As String) As LongPtr

Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageW" ( _
    ByVal hwnd As LongPtr, _
    ByVal wMsg As Long, _
    ByVal wParam As LongPtr, _
    lParam As Any) As LongPtr

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
    Destination As Any, _
    Source As Any, _
    ByVal Length As Long)

' Constant definitions
Private Const WH_CALLWNDPROCRET = 12
Private Const BM_CLICK = &HF5
Private Const IDOK = 1

Private Type CWPRETSTRUCT
    lResult As LongPtr
    lParam As LongPtr
    wParam As LongPtr
    message As Long
    hwnd As LongPtr
End Type

Private hHook As LongPtr

Public Sub SetDeleteAlertHook()
    ' Set up the hook to intercept window messages for the current Excel process
    If hHook = 0 Then
        hHook = SetWindowsHookEx(WH_CALLWNDPROCRET, AddressOf CallWndRetProc, 0, App.ThreadID)
    End If
End Sub

Public Sub RemoveDeleteAlertHook()
    ' Remove the hook to restore normal behavior
    If hHook <> 0 Then
        UnhookWindowsHookEx hHook
        hHook = 0
    End If
End Sub

Private Function CallWndRetProc(ByVal nCode As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Dim cwpRet As CWPRETSTRUCT
    Dim className As String
    Dim staticText As String
    Dim textLength As Long
    Dim buttonHwnd As LongPtr

    If nCode >= 0 Then
        CopyMemory cwpRet, ByVal lParam, LenB(cwpRet)
        
        ' Check if the window is a standard dialog box
        className = String(256, vbNullChar)
        GetClassName cwpRet.hwnd, className, 256
        className = Left(className, InStr(className, vbNullChar) - 1)
        
        If className = "#32770" Then ' Class name for standard Windows dialogs
            ' Get the prompt text inside the dialog
            Dim staticHwnd As LongPtr
            staticHwnd = FindWindowEx(cwpRet.hwnd, 0, "Static", vbNullString)
            If staticHwnd <> 0 Then
                textLength = GetWindowTextLength(staticHwnd)
                staticText = String(textLength, vbNullChar)
                GetWindowText staticHwnd, staticText, textLength + 1
                
                ' Verify it's the worksheet deletion prompt
                If InStr(staticText, "删除工作表将永久删除它。是否继续？") > 0 Then
                    ' Find and click the "OK" button
                    buttonHwnd = FindWindowEx(cwpRet.hwnd, 0, "Button", "&确定")
                    If buttonHwnd <> 0 Then
                        SendMessage buttonHwnd, BM_CLICK, 0, 0
                    End If
                End If
            End If
        End If
    End If
    
    ' Pass the message to the next hook in the chain
    CallWndRetProc = CallNextHookEx(hHook, nCode, wParam, ByVal lParam)
End Function

2. Paste this code into the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    ' Enable the hook when the workbook opens
    SetDeleteAlertHook
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Disable the hook when the workbook closes
    RemoveDeleteAlertHook
End Sub

Pros: #

• Covers all manual deletion scenarios (right-click, keyboard shortcuts, ribbon)
• Fully automatic, users won't notice any extra steps

Cons: #

• Relies on Windows API, requires compatibility with your Excel version (code is adapted for 32/64-bit)
• Macros must be enabled for this to work
• The hook only affects the current Excel instance

Important Notes: #

• Save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve the code
• Enable macros when opening the workbook (Excel will prompt you to do this)

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