防止用户删除特定的工作表 [英] Prevent user from deleting a particular sheet

问题描述

保护工作簿结构将阻止用户删除工作表。但是，如何（使用VBA）阻止用户删除我指定的特定表格？我已经看到了一个活动工作表被阻止删除的例子。

Protecting workbook structure will prevent a user from deleting sheets. But how could I (using VBA) prevent a user from deleting a particular sheet I designate? I've seen examples where an active sheet is prevented from deletion by

Set mymenubar = CommandBars.ActiveMenuBar
mymenubar.Controls("Edit").Controls("Delete sheet").Visible = False

code> Worksheet_Activate 事件，但当然只有在表单激活时才起作用。

有没有办法防止工作表被删除，无论是否活动？ br>
为了清楚起见，用户删除一些表单，只是不是几个特定的​​表格，这是很好的。

所以保护工作簿结构将无法正常工作。

in its Worksheet_Activate event but that of course only works if the sheet is activated.
Is there a way to prevent a sheet from being deleted whether active or no?
For clarity: I'm fine with the user deleting some sheets, just not a couple of particular sheets.
So protecting workbook structure won't work.

推荐答案

据我所知，这是不可能的，将一张单张贴到不可删除的位置;并且没有可以用于检测什么时候将要删除工作表的事件，从而可以预防性地保护工作簿。

As far as I can tell, it isn't possible to natively tag a single sheet as non-deletable; and there isn't an event that can be used to detect when a sheet is about to be deleted so the workbook can be protected preventively.

然而，这里有一个潜在的解决方法：

However, here is one potential workaround:

1. 保护工作簿结构：如你所说，将阻止所有的表格被删除。


2. 创建控件表。在此工作表中，维护所有工作表名称的列表（除了不想删除的工作表名称）。


3. 如果用户要删除工作表，则必须选择其名称（例如在数据验证下拉菜单中），然后按删除按钮。此按钮将调用临时取消保护工作簿的宏，删除所选工作表，然后重新保护工作簿。

当然，用户必须习惯这种删除工作表（而不是右键单击>删除工作表的选项卡）。不过，这并不是疯狂的复杂。

Of course, the users will have to get used to this way of deleting sheets (as opposed to just right-click > Delete on the sheet's tab). Still, this isn't crazy complicated.

至于如何实现＃2，即维护表名称列表，我想你可以使用像这样的UDF（必须称为数组公式）：

As for how to achieve #2 i.e. maintaining that list of sheet names, I suppose you could make use of a UDF like this one (must be called as an array formula):

Function DeletableSheetNames() As String()
    Application.Volatile
    Dim i As Long
    Dim sn() As String
    With ThisWorkbook
        ReDim sn(1 To .Sheets.Count)
        For i = 1 To .Sheets.Count
            With .Sheets(i)
                If .Name = "DataEntry1" Or .Name = "DataEntry2" Then
                    'Don't include it in the list.
                Else
                    sn(i) = .Name
                End If
            End With
        Next i
    End With
    DeletableSheetNames = sn
End Function

这篇关于防止用户删除特定的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！