Disabling items on the ribbon

Nick's posts on ribbon customization have been very helpful. However, I'm still having some difficulty. I wanted to disable most of the Save As choices on the Office button (except for .xlsm) and was able to figure that out. Now, though, I also want to disable the Move Or Copy Sheet command on the Format menu in the Cells group of the Home tab. I can make the entire Cells group disappear, so I know that I am accessing that correctly. However, I can't seem to get at the FormatCellsMenu menu and the items that it contains. From reading the docs it seems that the XML below (the part after </officeMenu>) should work, but it doesn't. All of my <officeMenu></officeMenu> customizations work fine. Any ideas?

 <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <officeMenu>
    <splitButton idMso="FileSaveAsMenu">
          <menu>
            <button idMso="FileSaveAsExcel97_2003" enabled="false"/>
            <button idMso="FileSaveAsExcelXlsb" enabled="false"/>
            <button idMso="FileSaveAsExcelXlsx" enabled="false"/>
            <button idMso="FileSaveAsMenu" enabled="false"/>
            <button idMso="FileSaveAsPdfOrXps" enabled="false"/>
            <button idMso="FileSaveAsOtherFormats" enabled="false"/>
        </menu>
    </splitButton>
    <splitButton idMso="FilePrintMenu" enabled="false"/>
    <menu idMso="FilePrepareMenu" enabled="false"/>
    <menu idMso="FileSendMenu" enabled="false"/>
    <menu idMso="MenuPublish" enabled="false"/>
    </officeMenu>
    <tabs>
      <tab idMso="TabHome">
        <group idMso="GroupCells">
        <menu idMso="FormatCellsMenu">
          <button idMso="SheetMoveOrCopy" enabled="false"/>
        </menu>
      </group>
      </tab>
    </tabs>
</ribbon>

I have bolded the part that doesn't seem to be working. Thanks for any advice.

Tim 

Tim

You need the commands attribute of the customUI (as explained in step 3), so something like...

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="SheetMoveOrCopy" enabled="false"/>
</commands>
<ribbon>
<officeMenu>
<splitButton idMso="FileSaveAsMenu">
<menu>
<button idMso="FileSaveAsExcel97_2003" enabled="false"/>
<button idMso="FileSaveAsExcelXlsb" enabled="false"/>
<button idMso="FileSaveAsExcelXlsx" enabled="false"/>
<button idMso="FileSaveAsMenu" enabled="false"/>
<button idMso="FileSaveAsPdfOrXps" enabled="false"/>
<button idMso="FileSaveAsOtherFormats" enabled="false"/>
</menu>
</splitButton>
<splitButton idMso="FilePrintMenu" enabled="false"/>
<menu idMso="FilePrepareMenu" enabled="false"/>
<menu idMso="FileSendMenu" enabled="false"/>
<menu idMso="MenuPublish" enabled="false"/>
</officeMenu>
</ribbon>
</customUI>

 Nick,

 Excellent! Thanks for that. It works perfectly.

So, how does one know when to use <commands> and when to use <menu> or <button>?  SheetMoveOrCopy is listed as a button in the ExcelRibbonControls.xlsx worksheet from Microsoft. There must be some logic to this, and you seem to have broken the code (thankfully).

Would I use <button idMso="SheetMoveOrCopy" enabled="false"/> to just disable the button for that command that can be added to the QAT?

I noticed that disabling SheetMoveOrCopy disables it on the sheet tabs and the QAT (if it was added there) as well as the Format menu. That is really great.

 BTW, this all came up because I give exams using Excel in my Financial Modeling class. For many years, I've used a bunch of VBA to keep students from doing certain things during the exam (like doing a SaveAs). It seems that I can now do some of that in the interface. Do you know where I can find a list of what qualifies as a <command> that can be disabled using the RibbonX code? I don't recall seeing anything like this on MSDN in the articles on modifying the ribbon.

 One thing that I really dislike about the new file formats is that saving as .xlsx will just strip out any VBA. That easily defeats all of my protection tools. That's why I needed to disable all of the SaveAs types other than .xlsm.

Tim 

Tim

We're all still learning this and I expect some considerable work to be done in the next release. As I understand it the <commands> attribute will disable that command wherever it appears, whereas the route you take with button will just do that control. Knowing your need now. I would have re-purposed the built in saveAs command by replacing the onAction attribute, like so

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <commands>
        <command idMso="FileSave" onAction="MySave"/>
    </commands>
</customUI>

Then you can code the callback MySave() allowing only the xlsm type

It is the usual 'more than one way to skin a cat' in computing

 Nick,

 That makes perfect sense. I was planning to try a few callbacks later today so I'll definitely give that a shot.

Do you know if it is possible to add comments to the xml in CustomUI? I can easily see me revisiting my new exam template six months from now and having to completely relearn what it all means. It is fairly self-explanatory, but it would still be nice to be able to comment the code.

BTW, I read another post where you were bemoaning the seeming lack of interest in modifying the ribbon. I think it is too early to tell, so I really hope that you will continue with your posts. I, for one, am seeing great possiblilties. Now that I've made some modifications, I'm really starting to like the ribbon more than I did before. I had already gotten used to using it, but now I'm seeing that it is pretty easy to program.

I'm planning to put a post on my blog in a few days pointing people to your blog for the "how to" of modifying the ribbon. Hopefully that will help drive a little traffic your way.

Tim 

Tim

Thanks for the comments, to comment xml just prefix and postfix as so

<!-- This is a comment -->

 Those comments are almost identical to the comments in the CMS that I use (ExpressionEngine) so that should be easy to remember.

Thanks again Nick. Much appreciated.

 Tim