Protect sheet from being deleted

[ulfal028]

Hi,
I'm looking for a way to protect certain sheets from being deleted. They may be modified by VBA or by the user, but never deleted.

Due to the amount of codes and subs of the actual workbook, the "traditional" protect/unprotect procedure (for each macro) is not an alternative.

[johnske]

 
Option Explicit
 
Private Sub Worksheet_Activate()
      With Application
            .CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete Sheet").Enabled = False
            .CommandBars("Ply").Controls("Delete").Enabled = False
      End With
End Sub
 
Private Sub Worksheet_Deactivate()
      With Application
            .CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete Sheet").Enabled = True
            .CommandBars("Ply").Controls("Delete").Enabled = True
      End With
End Sub

[ulfal028]

Now that's a truly brilliant solution! Many thanks. However, I assume the Controls("Edit") are defined according to a English version of Excel? I'm getting an error message (my translation):

Run Error nr '5'.:
Procedure call or argument not valid.

Any ideas? I'm not sure how to manually disable a control while recording a macro... The "Delete Sheet" msoControlButton has "ID:=847", that's all I seem to get while recording.

[johnske]

Yes, it's an english version, but just use the text that's wriiten on another language version - OR - try the number as: .Controls(847).Enabled = False

[gnod]

Hi,

when i right click the Sheet1 the delete command is disabled and that is the purpose of the code but when i right click Sheet2 the delete command is also disabled but i can delete the sheet

pls refer to the attached file..


thanks..

[johnske]

Hi,

when i right click the Sheet1 the delete command is disabled and that is the purpose of the code but when i right click Sheet2 the delete command is also disabled but i can delete the sheet

pls refer to the attached file..


thanks..

It only shows as being disabled because you used the right-click to activate sheet2 - but it's actually not (and shouldn't be) so you can delete it. If you activate sheet2 with a left-click this doesn't happen