Prevent Tabs /Sheets being added or deleted

[gheyman]

Is there a way to prevent sheets from being deleted or added to a workbook?

Thanks
Greg in Orlando

 

[Von Pookie]

Have you tried protecting the workbook?

 

[gheyman]

Prevent Tabs /Sheets from being added or deleted

Thanks, but I can't protect the workbook - its multi-user (Shared)

 

[daniels012]

What about protecting the workbook, then unprotecting all areas the users need to work in??

Just an idea,
Michael

 

[Tom Urtis]

Taking away the menu items and the paths to them might do it for you.

Place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.


Private Sub Workbook_Activate()
With Application
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete Sheet").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = False
.CommandBars("Toolbar List").Enabled = False
.CommandBars("Ply").Enabled = False
End With
End Sub


Private Sub Workbook_Deactivate()
With Application
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete Sheet").Enabled = True
.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = True
.CommandBars("Toolbar List").Enabled = True
.CommandBars("Ply").Enabled = True
End With
End Sub


Save the workbook, then either
- close it and re-open it
or
- activate another Excel workbook (open in that instance of Excel) and re-activate the subject workbook

 

[gheyman]

Thank You One and All

Toms suggestion will do the trick. Thanks, I didnt think of that.

Greg

 

[ndendrinos]

Edit/Move or copy Sheet needs to be disabled too ... any idea how to add this to Tom's code ?
I understand that I will lose the option of moving or copying the sheet but in my situation the need to disable the adding/deleting or renaming a sheet is way more important.
Regards,
Nick

 

[Tom Urtis]

This should do it:


Private Sub Workbook_Activate()
With Application
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Move or Copy Sheet...").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete Sheet").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = False
.CommandBars("Toolbar List").Enabled = False
.CommandBars("Ply").Enabled = False
End With
End Sub


Private Sub Workbook_Deactivate()
With Application
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Move or Copy Sheet...").Enabled = True
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete Sheet").Enabled = True
.CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Worksheet").Enabled = True
.CommandBars("Toolbar List").Enabled = True
.CommandBars("Ply").Enabled = True
End With
End Sub

 

[ndendrinos]

Thank you Tom.