vba 防止用户删除特定工作表

Prevent user from deleting a particular sheet

提问by user3474688

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

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

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

in its Worksheet_Activateevent 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.

在它的Worksheet_Activate事件中，但当然只有在工作表被激活时才有效。
有没有办法防止工作表被删除，无论是否处于活动状态？
为清楚起见：我可以让用户删除一些工作表，而不是一些特定的工作表。
所以保护工作簿结构是行不通的。

采纳答案by Jean-Fran?ois Corbett

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. Protect workbook structure: this will, as you indicate, prevent all sheets from being deleted.
2. Create a "Controls" sheet. On this sheet, maintain a list of all sheet names (except those you don't want to be deletable).
3. If users want to delete a sheet, they will have to select its name on the Controls sheet (e.g. in a data validation drop-down menu) and press a "Delete" button. This button will call a macro that temporarily unprotects the workbook, deletes the selected sheet, and then reprotects the workbook.

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.

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

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

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

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

回答by jemenfou

You cannot stop users to delete a particular sheet but you could use the Workbook_BeforeSave()event to prevent the workbook from being saved if a particular sheet is missing. The documentation on this event precisely shows how to allow saving a workbook only when certain conditions are met. See http://msdn.microsoft.com/en-us/library/office/ff840057(v=office.14).aspx

您无法阻止用户删除特定工作表，但您可以使用该Workbook_BeforeSave()事件来防止在丢失特定工作表时保存工作簿。此事件的文档准确地显示了如何仅在满足某些条件时才允许保存工作簿。请参阅http://msdn.microsoft.com/en-us/library/office/ff840057(v=office.14).aspx

回答by Dan McCormick

I can prevent a sheet from being deleted via the Worksheet_BeforeDelete Event as follows:

我可以防止通过 Worksheet_BeforeDelete 事件删除工作表，如下所示：

Private Sub Worksheet_BeforeDelete()

    Call ThisWorkbook.Protect("password")

    Call MsgBox("This sheet cannot be deleted.", vbExclamation)

End Sub

This protects all sheets from being deleted, however if you add some event code on the ThisWorkbook module like the following :

这可以保护所有工作表不被删除，但是如果您在 ThisWorkbook 模块上添加一些事件代码，如下所示：

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Call ThisWorkbook.Unprotect("password")

End Sub

I will then be able to delete any other sheet as soon as it is selected.

然后，我将能够在选择任何其他工作表后立即将其删除。

Bear in mind, you will lose copy and paste functionality between pages due to the page unlocking when it is selected.

请记住，由于在选择页面时页面解锁，您将失去页面之间的复制和粘贴功能。

回答by Shawn V. Wilson

I found this solution, similar to Dan's, on ExtendOffice.com. Put this code on the Worksheet's module:

我在 ExtendOffice.com 上找到了类似于 Dan 的解决方案。将此代码放在工作表的模块上：

Private Sub Worksheet_Activate()
ThisWorkbook.Protect "yourpassword"
End Sub

Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect "yourpassword"
End Sub

When you activate the sheet in question, the whole workbook is protected, and the "Delete" option is grayed out. When you switch to any other sheet, the workbook is free again. It's subtle because you only notice the change when you go to the "safe" sheet.

当您激活有问题的工作表时，整个工作簿都受到保护，并且“删除”选项变灰。当您切换到任何其他工作表时，工作簿将再次空闲。这很微妙，因为您只有在转到“安全”表时才会注意到变化。

回答by Travis Banger

"there isn't an event that can be used to detect when a sheet is about to be deleted"

“没有可用于检测工作表何时将被删除的事件”

Since Office 2013, it is possible with the SheetBeforeDeleteevent.

自 Office 2013 起，可以使用SheetBeforeDelete事件。

回答by Francisco Costa

Answer is by adding the following code to each of the protected sheets:

答案是将以下代码添加到每个受保护的工作表中：

Private Sub Worksheet_Deactivate()
    ThisWorkbook.Protect , True
    Application.OnTime Now, "UnprotectBook"
End Sub

And the following to a Module:

以及模块的以下内容：

Sub UnprotectBook()
    ThisWorkbook.Unprotect
End Sub

Check https://www.top-password.com/blog/prevent-excel-sheet-from-being-deleted/for credits accordingly.

相应地检查https://www.top-password.com/blog/prevent-excel-sheet-from-being-deleted/以获取积分。