asked on

Excel 2010 How to hide the Quick Accesss Toll via Vba or via Custom UI Editor

Hello,
This is for Excel 2010
I need to find the way to secure an Excel file.
I have already find how to disable / hide Save Save As Print Copy Paste in all kind of menu or item.
1) How to hide the QAT for me to make impossible to the save item or to rapid print item etc or to modify the QAT
i need to do this or via a vba macro or via the custom UI Editor
2) How to disable in the Ribbon the right's mouse menu because it will make possible to add print item or save etc...
All changes are needed only for this workbook and not for all Excel environment.
Thanks you

dlmille

To remove from the QAT, MSFT chose to disable this capability (to make simple changes - re: add/remove controls from the QAT), and restricts QAT customization to Dictator Apps that also set the startFromScratch parameter to TRUE. This, unfortunately, would entail rebuilding the built-in tabs and menu items and having no QAT except for those items you added via XML, as a result.

Here's a link to Ron deBruin's website on the subject:
http://www.rondebruin.nl/ribbon.htm

In this example (which works in both Excel 2007 and 2010), we have started from scratch with the ribbon (thus the QAT is NOT available and CANNOT be added if this workbook is active). I've eliminated your save,save as and print options from the office menu (Excel 2007) and backstage (Excel 2010).

With this, if I've caught everything, you have no need to disable the right mouse menu, because you should not be able to invoke a save or print.

Here's the XML that does all the work (tested with Excel 2007, and I'll test with 2010 this evening, but believe everything is in order):

Excel 2007:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <officeMenu>	
       <button idMso="FileNew" visible="true"/>	
       <button idMso="FileOpen" visible="true"/>	
       <button idMso="FileSave" visible="false"/>	
       <splitButton idMso="FileSaveAsMenu" visible="false"/>
       <splitButton idMso="FilePrintMenu" visible="false"/>	
       <menu idMso="FilePrepareMenu" visible="true"/>	
       <menu idMso="FileSendMenu" visible="true"/>	
       <menu idMso="MenuPublish" visible="true"/>
       <button idMso="FileClose" visible="true"/>	
  </officeMenu>
  <ribbon startFromScratch="true">
	<tabs>
       <tab idMso="TabHome" visible="true"/>
       <tab idMso="TabInsert" visible="true"/>
       <tab idMso="TabPageLayoutExcel" visible="true"/>
       <tab idMso="TabFormulas" visible="true"/>
       <tab idMso="TabData" visible="true"/>
       <tab idMso="TabReview" visible="true"/>
       <tab idMso="TabView" visible="true"/>
       <tab idMso="TabDeveloper" visible="true"/>
       <tab idMso="TabAddIns" visible="true"/>
       <tab idMso="TabFormulas" visible="true"/>
       <tab idMso="TabData" visible="true"/>
       <tab idMso="TabReview" visible="true"/>
      </tabs>   
  </ribbon>
</customUI>

Open in new window

Excel 2010:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <backstage>	
       <button idMso="FileSave" visible="false"/>	
       <button idMso="FileSaveAs" visible="false"/>	
       <button idMso="FileOpen" visible="true"/>	
       <button idMso="FileClose" visible="true"/>	
       <button idMso="ApplicationOptionsDialog" visible="true"/>	
       <button idMso="FileExit" visible="true"/>	
       <tab idMso="TabInfo" visible="true"/>	
       <tab idMso="TabRecent" visible="true"/>	
       <tab idMso="TabNew" visible="true"/>	
       <tab idMso="TabPrint" visible="false"/>	
       <tab idMso="TabShare" visible="true"/>	
       <tab idMso="TabHelp" visible="true"/>	
  </backstage>
  <ribbon startFromScratch="true">
	<tabs>
       <tab idMso="TabHome" visible="true"/>
       <tab idMso="TabInsert" visible="true"/>
       <tab idMso="TabPageLayoutExcel" visible="true"/>
       <tab idMso="TabFormulas" visible="true"/>
       <tab idMso="TabData" visible="true"/>
       <tab idMso="TabReview" visible="true"/>
       <tab idMso="TabView" visible="true"/>
       <tab idMso="TabDeveloper" visible="true"/>
       <tab idMso="TabAddIns" visible="true"/>
       <tab idMso="TabFormulas" visible="true"/>
       <tab idMso="TabData" visible="true"/>
       <tab idMso="TabReview" visible="true"/>
      </tabs>   
  </ribbon>
</customUI>

Open in new window

Both are embedded in the attached file, which you can use for testing.

Please let me know if this works for you.

See attached.

Dave
NoQATRebuildTabs.xlsx

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bruno_boccara

ASKER

Hello,
Thank you for your answer!
I have tried but it didn't work, when i have open the custom UI Editor and wants to validate the code it displays me an error (see the joined file it's in french)
It says something like "has an invalid child element ribbon" and "List of possible elements expected ContextMenus"

Can you help me how to resolve it?
Thank you

dlmille

There's nothing attached.

I'm sorry I appear to have dropped the ball on this one. Can you upload the offending file so I can help "repair"?

Dave

bruno_boccara

ASKER

sorry
I upload the file after some changes in a few hour