Control Name for Insert tab

Question

I am trying to design a workbook with some restrictions without using VBA in Excel, which is compatible in 2007 and 2010. I have chosen "Custom UI Editor For Microsoft Office" with XML code to restrict a few options:- Save-as with info tab, Insert, Delete, Move/Copy Sheet, Hide sheet, Unhide sheets. I was successful in doing so but I have noticed that insert sheet tab "ICON"

is still working and is accessible. Can anyone point me to the Control Name to disable it through XML in file please?

My code is:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <commands>
               <command idMso="FileSaveAsWebPage" enabled="false" />
               <command idMso="FileSaveAs" enabled="false" />
               <command idMso="FileSaveAsMenu" enabled="false" />
               <command idMso="FileSaveAsExcelXlsx" enabled="false" />
               <command idMso="FileSaveAsExcelXlsxMacro" enabled="false" />
               <command idMso="FileSaveAsExcel97_2003" enabled="false" />
               <command idMso="FileSaveAsExcelOpenDocumentSpreadsheet" enabled="false" />
               <command idMso="FileSaveAsPdfOrXps" enabled="false" />
               <command idMso="FileSaveAsOtherFormats" enabled="false" />

               <command idMso="SheetInsert" enabled="false" />
               <command idMso="SheetInsertPage" enabled="false" />
               <command idMso="SheetDelete" enabled="false" />
               <command idMso="SheetRename" enabled="false" />
               <command idMso="SheetMoveOrCopy" enabled="false" />
               <command idMso="SheetUnhide" enabled="false" />
               <command idMso="SheetProtect" enabled="false" />
               <command idMso="SheetTabColorGallery" enabled="false" /> 
               <command idMso="SheetTabColorMoreColorsDialog" enabled="false" />
             <command idMso="SelectAllSheets" enabled="false" />
  </commands>
  <backstage>
           <tab idMso="TabInfo" visible="false"/>
  </backstage>
</customUI>

I have tried searching Microsoft and rondebruin for Office Fluent User Interface Control Identifiers also.

Answer 1

For this to be done through XML you would need to be able to access the element - it needs to have an ID. Manually scanning through the various lists that Microsoft published turned up nothing helpful, but since their documentation is notoriously sloppy I decided to write a very small piece of code that finds the ID of "every control with an ID" in the Excel application, and lists it:

Sub listID()
Dim r As Range
Dim ctls
Dim ii As Long

Cells(1, 1).Value = "ID"
Cells(1, 2).Value = "caption"
Cells(1, 3) = "Type"
Set r = Range("a1")

For ii = 1 To 100000
  Set ctls = CommandBars.FindControl(Id:=ii)
  If Not (ctls Is Nothing) Then
  'Debug.Print "controls ID " & ii & " exists; the caption is " & ctls.Caption & "; the type is " & ctls.Type
  Set r = r.Offset(1, 0)
  r.Value = ii
  r.Offset(0, 1) = ctls.Caption
  r.Offset(0, 2) = ctls.Type
  r.Offset(0, 3) = ctls.TooltipText
  End If
Next ii

End Sub

After I run this, and filter on anything with eet in the name, I would expect to see all the controls "that can be controlled" (because they have an msoID) and that relate to "Sheets". The following is the snapshot of what this produces:

When I hover my mouse over the "button" you want to hide, I get the toolTip "Insert Sheet" - which isn't any of the ones I can see in my list. I conclude from this that it is indeed impossible to do what you are asking - you cannot disable that button with XML.

That doesn't mean you can't achieve what you would like. I would suggest the following approaches.

1. Trap the workbook event that is triggered when a new sheet is created, and delete it on the spot. When the button "stops working" people will soon give up. Example code below.
2. Hide the sheet tabs completely, and provide an alternative method of navigating between sheets. Since this is obviously a "controlled spreadsheet" that may be a good idea anyway. You could either create a custom tab on the ribbon (using XML, you seem to be familiar with that), or create a floating toolbar that lives at the bottom of the sheet - close to where the "old" tabs used to be. In that way you simulate the behavior - but it's a lot of work and a bit of a hack
3. Add protection to the workbook. Use Protection -> Protect Workbook -> "Protect structure": sheets can not be moved, deleted, hidden, unhidden, or renamed. New sheets cannot be inserted.

The code you would have to add to ThisWorkbook is:

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

Once this is in your workbook, any time a user clicks the "new sheet" button there will be a very brief flash, but no new sheet is created. You could add a Application.ScreenUpdating = False but the brief flash remains...

Sorry I don't have better news for you.