Disabling Delete Sheet from Menu

[jp77]

How do I diable the "Delete Sheet" so user can not delete any sheets

THANK YOU!!!

JP77

 

[Smitty]

The easiest way is to protect the worbook.

HTH,

Smitty

 

[jp77]

I can't because it has to allow worksheet names to be changed while not allowing to delete any sheets

THANK YOU!!!

 

[Smitty]

You can use a sheet change event to tie the sheet name to a range on each sheet, or give the user the option to unprotect/rename/reprotect, or disable the menu altogether.

But there you're talking about code solutions that can easliy be disabled.

Which would you prefer?

Smitty

 

[jp77]

I prefer the CODE THANK YOU!!

 

[Smitty]

This is probably the easiest way:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> RenameSheet()
    <SPAN style="color:#00007F">Dim</SPAN> ShtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    ActiveWorkbook.Unprotect "PasswordHere"
        ShtName = InputBox("Enter New Sheet Name", "Rename Sheet")
        ActiveSheet.Name = ShtName
    ActiveWorkbook.Protect "PasswordHere"
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

NOTE: there's no error handling for illegal sheet name characters.

Smitty

 

[jp77]

It didn't work.


I have the below code that will not allow adding any new sheets. I need to add codes to disable Delete Sheet while allowing to rename sheets in the file... THANK YOU!!!! JP77

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[Smitty]

If you protect the workbook, you can't add sheets or delete them, and you won't need the code that you posted.

The code I posted is a workaround to being able to name sheets with the wb protected.

Smitty

 

[jp77]

It doesn't quite work. May be I can explain in more detail

I have 3 sheets in a workbook that are used as input sheet.

1. Users can not insert a new sheet (which is taken care with the Private Sub)
2. Users are NOT ALLOWED to delete any of sheets contained in the report
but ALLOWED to change the names any of the 3 sheets in the workbook.
(this, #2, is the one I need your help)

THANK YOU! JP77

 

[Smitty]

2. Users are NOT ALLOWED to delete any of sheets contained in the report
but ALLOWED to change the names any of the 3 sheets in the workbook.

Again, if you protect the workbook, you can't add sheets or delete them, and you won't need the code that you posted.

The code I posted is a workaround to being able to name sheets with the wb protected.

How does it not do what you need? Just saying "it doesn't quite work" doesn't lead me to a different solution.

Smitty