Prevent Deletion of Specific Worksheet in Excel 2013

[amisagi]

Hi - Can any body help to prevent users from deleting/ move/ copy a specific worksheet in excel 2013.
There are many examples for earlier versions and they dont work on 2013.

Many thanks in advance!

 

[mole999]

can you make the sheet xlsheetveryhidden

 

[amisagi]

Thanks Mole99, but "hiding" the sheet is not an option as it is used to take inputs from users.
Protecting the workbook is also not feasible as users need to be able to add/ delete /modify other sheets in the workbook.

i have been working on something, but i am getting Error 438 - object does not support property or method

Private Sub Worksheet_Activate()


Dim cbs As CommandBarControl
Dim cmb As CommandBar


Set cmb = Application.CommandBar("&Edit")
Set cbs = cmb.FindControl(ID:=848)


MsgBox cbs.Caption


End Sub

 

[mole999]

you can lock all cells on the sheet, then unlock the ones you will allow, (a little trial and error)

I think there must be routine that can intercept a sheet delete, providing macros are enabled

 

[amisagi]

Yes, thats what i am hoping to find - some way to intercep a sheet delete...
i dont want it reactive, like file cant be saved if sheet is deleted etc... it really needs to be proactive so users cant mess up the file...

 

[amisagi]

Re: Prevent Deletion of Specific Worksheet in Excel 2013 *** SOLVED ***

Ok, I have finally figured out a solution to this. Putting this in for someone else in same situation as me.

If any one has a better solution - please share.

Since i couldnt find a way to manupulate the command bars and prevent someone from deleting my main sheet, i deviced a workaround.

a) Keep a secret identifier string on a cell of the main sheet.
Since this sheet is used for inputs & calculation, i know this cell will never be used
Make sure your worksheet is "locked", "hidden" and password protected to prevent users from knowing your secret code or messing around the main calculations/ input sheet.

b) Create a copy of the sheet with "activesheet.copy" when ever someone tries to delete my main sheet!
This will create something like Sheet1 (2), Sheet1 (3), Sheet1 (4) etc & Sheet1 gets deleted.

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)


[COLOR=#008000]'name of main sheet = "ShButtons"[/COLOR]


Dim SheetFullName As String
Dim SheetParsedName As String


With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With


[COLOR=#008000]'get the full name of the sheet being deleted[/COLOR]
SheetFullName = ActiveSheet.Name


[COLOR=#008000]'get the first 9 letters of the name of sheet being deleted[/COLOR]
[COLOR=#008000]'ShButtons has 9 letters; this is ensure code works for copies of copies as well[/COLOR]
SheetParsedName = Left(SheetFullName, 9)
    
[COLOR=#008000]'match first 9 letters and secret code[/COLOR]
[COLOR=#008000]'copy sheet only is both matches[/COLOR]
[COLOR=#008000]'let user delete all else[/COLOR]
If SheetParsedName = "ShButtons" And ActiveSheet.Range("L2").Value = "mysecretstring@99" Then
    ActiveSheet.Copy After:=Sheets(ActiveWorkbook.Worksheets.Count)
End If
       
End Sub

c) Next insert a code such that when someone tries to use the file, it would first check for the secret identifier code and rename any sheet that contains that code to the original name.
PreventNameChange() is a function that can be called at any place... just place Call PreventNameChange on a "click" or "activate" event.
In my case I was using an userform, so

Private Sub UserForm_Activate()
    Call PreventNameChange
End Sub

So Sheet1 (2) will now be renamed as Sheet1

Sub PreventNameChange()


Dim i As Integer


For i = 1 To ThisWorkbook.Worksheets.Count
    Worksheets(i).Activate
    If Range("L2").Value = "mysecretstring@99" Then
        If ActiveSheet.Name <> "ShButtons" Then
            ActiveSheet.Unprotect "password" 
            ActiveSheet.Name = "ShButtons"
            ActiveSheet.Protect "password"
            Exit Sub
        End If
    End If
Next i


End Sub

Hope this helps someone...

 

[simurq]

Re: Prevent Deletion of Specific Worksheet in Excel 2013 *** SOLVED ***

amisagi,

That's a great workaround!!!

I've slightly modified your last piece of code so that the sheet is renamed to its original name on another ThisWorkbook event (without a need for extra subroutine). In my case it is SheetSelectionChange. Any cell clicked by the user will trigger the below code to rename the sheet:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim i As Integer

For i = 1 To ThisWorkbook.Worksheets.Count
'    Worksheets(i).Activate
    If Worksheets(i).Range("L2").Value = "[COLOR=#574123]mysecretstring@99[/COLOR]" Then
        If Worksheets(i).Name <> "[COLOR=#574123]ShButtons[/COLOR]" Then
            Worksheets(i).Name = "[COLOR=#574123]ShButtons[/COLOR]"
            Exit Sub
        End If
    End If
Next i

End Sub