PDA

View Full Version : Protect sheet from being deleted



ulfal028
09-12-2006, 04:17 AM
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
09-12-2006, 04:48 AM
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
09-12-2006, 05:40 AM
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
09-12-2006, 06:06 AM
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
09-12-2006, 06:44 AM
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 :confused:

pls refer to the attached file..


thanks..

johnske
09-12-2006, 07:02 AM
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 :confused:

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