How to Stop deleting a worksheet however ALLOW Renaming the worksheet?

[excelsion]

I have a spreadsheet with 50 worksheets. The information from 49 of those feeds into the 50th worksheet. I am wary that someone could delete one of the 49 worksheets and the calculations can go awry.

How do i stop someone from deleting the worksheet HOWEVER allowing them to still rename the worksheet?

I have read a few posts..on Protecting the Workbook or using VBA to stop deletion...however they also restrct renaming the worksheets , hence it doesnt work in my case.

Please suggest a solution.

Thanks

 

[NMa91]

This prevents sheet being deleted.

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
ThisWorkbook.Protect "password"
End Sub

 

[excelsion]

This prevents sheet being deleted.

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
ThisWorkbook.Protect "password"
End Sub

Where do i put this code? I tried the following:

Right click on the worksheet
Select VIEW CODE
PAsted the code in the white section of the screen
Saved the file
Tried deleting the worksheet and the code didnt work and the worksheet was deleted.

 

[NMa91]

Press alt + F11. Select Thisworkbook from left. right click, then select View code. paste it. It is workbook module.

 

[Jaafar Tribak]

Workbook_SheetBeforeDelete only availble on excel 2013 and later editions.