Any way to prevent deleting a specific WS?

[Paul_Hossler]

Is there any way to prevent a specific WS from being deleted?

Sort of like a WB event called Workbook_BeforeWSdelete?

I has a WB that has a WS that the user should not delete, but they can add/delete others so protecting the WB probably won't work.

I'll take any out of the box ideas or approaches.

Paul

[GTO]

Greetings Paul,

Maybe not "perfect," but presuming factory commandbars are being used, rather than trying to delete the sheet after the command is executed - how about re-directing what happens when Edit | Delete Sheet is pressed/clicked?

You may wish to read thru:
http://www.mrexcel.com/forum/showthr...rksheet+delete

If this sounds worthy of a try, compliments of rorya's code at post #11:
In a Standard Module:
[vba]
Option Explicit

Sub Hook_Delete()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars.FindControls(ID:=847)
ctl.OnAction = "'MySheetDelete ActiveSheet.CodeName'"
Next ctl
End Sub

Sub Unhook_Delete()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars.FindControls(ID:=847)
ctl.OnAction = ""
Next ctl
End Sub

Sub MySheetDelete(WSCodeName As String)
If Not WSCodeName = shtProtected.CodeName Then
ActiveSheet.Delete
Else
MsgBox "STOP! Worksheet " & ActiveSheet.Name & " may not be deleted etc...", _
vbCritical, "YEEKS!"
End If
End Sub
[/vba]

In ThisWorkbook Module:
[vba]
Option Explicit

Private Sub Workbook_Activate()
Call Hook_Delete
End Sub

Private Sub Workbook_Deactivate()
Call Unhook_Delete
End Sub
[/vba]

Mark

Edit: Sorry, forgot to mention, I gave the sheet being protected from deletion the codename: shtProtected

[Paul_Hossler]

Yesterday at 11:03 PM. Reason: Three edits! If I get any more brain-dead, I'll be sittin' here in drool...

LOL -- been there, done that

Possibilities there, not using commandbars, (2007), but maybe I can hook into the command.

There's 2 or 3 ways that a WS can be deleted I guess, but I think it's an approach.

Be much easier if MS had some events that could be used

Thanks

Paul

[Paul_Hossler]

GTO -- works a treat !!!

Requirements creep -- can I hook the WS rename also?

Also, in

[vba]
Sub Hook_Delete()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars.FindControls(ID:=847)
ctl.OnAction = "'MySheetDelete ActiveSheet.CodeName'"
Next ctl
End Sub
[/vba]

It finds 3 ctl's and sets their .OnAction. Is it normal to find 3 places for ID=847?

Paul

[GTO]

Hey Paul,

Glad that seems to work I was worried when you mentioned 2007, as I don't have and the only thing I've done with ribbon is tie a gift up...

As to finding the delete command (or more accurately the ID) three places, up to 2003, I believe these are on Worksheet Menu Bar, Chart Menu Bar, and Ply ("Ply" is the right-click menu for the sheets' tabs)

As to preventing the renaming of the sheet(s), I presume you're only trying to prevent certain specific sheets from being renamed, similar to preventing certain sheets from being deleted.

I didn't write a test for a specific specific sheet rename, but I think this should prevent any sheet from being renamed. I would think you could tack in a Boolean and have it reverse the rename IF the sheet was one of interest...

This of course doesn't actually prevent renaming; just changes it back upon leaving the sheet. If you added the test/change back to a couple of more places (before save I'm thinking) it would seem halfway decent, but it does seem I'm overlooking some better way.

Anyways, try this and see if it seems safe enough, and maybe in the meantime someone will suggest something hardier.
[vba]
Option Explicit

Dim strWKSName As String

Private Sub Workbook_Open()
strWKSName = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
strWKSName = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Not Sh.Name = strWKSName Then
Sh.Name = strWKSName
MsgBox "How would you like to jack-up your own projects instead of my hard work?" _
& vbCrLf & _
"Quit renaming sheets, or next, I'll install an ""Auto-emailer"" in which" _
& vbCrLf & _
"you'll ""accidently"" cc the boss a note calling his wife a cow...", _
vbExclamation, vbNullString
End If
End Sub
[/vba]

Hope this helps a little,

Mark

[Paul_Hossler]

Hope this helps a little,

Mark

Helps a lot -- it's not a 100% solution, but it's at least a 98% solution

I did change the error message tho

Thanks

Paul