Protect specific sheets from deletion

**jubi23** · 12-19-2007, 03:52 PM

Is there anyway to prevent certain sheets from be deleted?

i know there is protect workbook, but that prevents the user from deleting any sheet

**VBA Noob** · 12-19-2007, 04:11 PM

See if example helps

Code used

In sheet1 module (right click sheet1 rab > select view code)

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 = "PreventDelete"
      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

Code in a normal module

Public Sub PreventDelete()

    MsgBox "Sorry, Cannot delete this sheet!", _
    Buttons:=vbExclamation, _
    Title:="Cannot Delete Sheet!"
End Sub

VBA Noob

**jubi23** · 12-19-2007, 04:16 PM

awesome, thanks man!

**jubi23** · 12-19-2007, 04:35 PM

so uh how do i turn it off? lol
i ran it on a sheet i do want to be able to delete and can't figure it out ha

**VBA Noob** · 12-19-2007, 04:47 PM

Code is triggered when you select or unselected sheet1. Unselect sheet1 to turn off

VBA Noob

**jubi23** · 12-19-2007, 04:51 PM

so uh, i tried making a new workbook entirely, and the macro followed it there

for example, lets say i select sheet3, and then right click to try and delete, it pops open the excel with the preventdelete macro and doesn't allow me to do even if i disable macros