Prevent user from deleting a particular sheet

Question

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

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.

Answer 1

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.

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

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