Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to disable add sheet button in Excel with VBA?

Tags:

excel

vba

I have a workbook I would like to disable the Add New Sheet button that is next to the tabs. I have searched and found the following that disable the insert options on the workbook book which is great.

Application.CommandBars("Ply").FindControl(, 945).Enabled = False 
Application.CommandBars("Insert").Controls(4).Enabled = False  

But I have yet to find the command for the Add New Sheet button. Is there a place that lists all these options or a tool I can use to identify the control or button.

The workbook is shared so automatically deleting the sheet on creation will not work.

Protect Structure does not work either and throws the following error:

enter image description here

like image 658
Matt Avatar asked Oct 21 '25 11:10

Matt


1 Answers

In the ThisWorkbook code sheet, paste the following.

Option Explicit

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

Any new (or copied) worksheet that is created is instantly deleted.