Prevent Delete Worksheet Problem

[bentleybob]

An old post provided the following macros that purportedly prevent users from deleting a worksheet. Unfortunately, it also makes it so you can't delete ANY worksheet in ANY file, which is of course not what was intended. Can anybody help me figure a way to remove this nuisance? All attempts to delete a sheet keep looking back to the offending workbook, even though the macro has since been deleted from it.

Thanks.


Here's what was posted:
_____________________
Try pasting the following two event procedures into the Help sheet module:

'==========================>>
Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then
Ctrl.OnAction = "RefuseToDelete"
Ctrl.State = msoButtonUp
End If
Next
End Sub
'-----------------------------------

Private Sub Worksheet_Deactivate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then Ctrl.OnAction = ""
Next
End Sub
'<<==========================
The above represents worksheet event code and should be pasted into the Help
sheets's code module (not a standard module and not the workbook's
ThisWorkbook module):

************************************************************
Right-click the Help sheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
************************************************************

Now paste the following sub into a standard module:

'==========================>>
Public Sub RefuseToDelete()
MsgBox "This help sheet should not be deleted!", _
Buttons:=vbExclamation, _
Title:="Cannot Delete Help!"

End Sub

[romperstomper]

You could try running a reset on it:

Please Login or Register  to view this content.

[bentleybob]

Nope, that doesn't work.

[romperstomper]

Sorry - typo in there. Run this:

Please Login or Register  to view this content.

and remove all the worksheet activate event codes from your workbook.

[bentleybob]

Sorry, YES that did work. THANKS!!