vba - How do you disable "Save and send" in Excel 2010 (in the File ribbon (called backstage in Office 2010)?

Question

I have the following VBA marco running in Excel 2003, it blocks the Save, Save as, Save Workspace, Send To menus but I now need to do the same but for Excel 2010 ?

Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False

Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False

Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save Workspace...").Enabled = False

Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Send To").Enabled = False

Application.CommandBars("Worksheet Menu Bar").Controls("Format").Controls("Sheet").Enabled = False

There is a 'new menu' in Excel 2010 called "Save and send" (under the File ribbon) that I would like to also disable... How do you do this ? I’m searching for the equivalent to a line like this one:

'Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save & Send").Enabled = False

Thank you for your help.

Answer 1

For Ribbon UI example/documentation from Microsoft, here. They will recommend you use the CustomUI Editor.

Open your file (which must be an Excel 2007+ file extension like XLSX, XLSM, XLAM, etc) in the CustomUI Editor. Then use this XML in the editor:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<backstage>
    <tab idMso="TabShare" visible="false" enabled="false">
    </tab>
</backstage>
</customUI>

Save the file, and close the CustomUI Editor.

Then, open your file. Go to the File/Backstage view and you should see that the Save and Send tab has been removed:

Note: This has disabled the entire "Save & Send" tab from the backstage view. It should be possible using XML to fine-tune this, i.e., to enable the tab, but disable specific controls therein (e.g., maybe you only want to disable Send as Attachment but you want to leave the other options enabled, etc.) but I haven't quite worked out the XML hierarchy to disable only the specific Send As Attachment control.

Hope this helps!

Update from Comments

1) The user will find "normal" functionality when this file is closed, or when another file is Active. The UI customization only applies to the file which contains the Custom UI XML parts, so if you have multiple files open, only this file will be affected by the disabled Save & Send tab.

2) Unfortunately, this is not compatible with Excel 2003 or prior. The CustomUI/XML is not a part of the legacy XLS files, and as such, cannot be implemented in these versions. For 2003/prior versions of Excel, you will have to use the legacy CommandBars which can be controlled through VBA. It is relatively easy to put some VBA code that will check which version of Excel, and run a subroutine to disable certain CommandBar/Controls only if the user is in 2003 or prior versions.

If you try to save this down to an XLS file, you will receive a warning, indicating that this feature is incompatible with the file type:

Additionally, you cannot open an XLS file in the CustomUI Editor.