Add ribbon programmatically to Excel 2010 using VBA

**heapifyman** · 07-15-2011, 02:54 PM

Hello,

I would like to create and add a custom ribbon to Excel 2010 using VBA. I have found this page: http://msdn.microsoft.com/en-us/library/ee767705.aspx but the following code does not work in Excel because obviously "ActiveProject.SetCustomUI (ribbonXml)" does not exist in Excel. So now I'm looking for an Excel equivalent to that line, which I simply cannot find anywhere. Any help would be appreciated. Thanks

Code:

Private Sub AddHighlightRibbon()
    Dim ribbonXml As String
    
    ribbonXml = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
    ribbonXml = ribbonXml + "  <mso:ribbon>"
    ribbonXml = ribbonXml + "    <mso:qat/>"
    ribbonXml = ribbonXml + "    <mso:tabs>"
    ribbonXml = ribbonXml + "      <mso:tab id=""highlightTab"" label=""Highlight"" insertBeforeQ=""mso:TabFormat"">"
    ribbonXml = ribbonXml + "        <mso:group id=""testGroup"" label=""Test"" autoScale=""true"">"
    ribbonXml = ribbonXml + "          <mso:button id=""highlightManualTasks"" label=""Toggle Manual Task Color"" "
    ribbonXml = ribbonXml + "imageMso=""DiagramTargetInsertClassic"" onAction=""ToggleManualTasksColor""/>"
    ribbonXml = ribbonXml + "        </mso:group>"
    ribbonXml = ribbonXml + "      </mso:tab>"
    ribbonXml = ribbonXml + "    </mso:tabs>"
    ribbonXml = ribbonXml + "  </mso:ribbon>"
    ribbonXml = ribbonXml + "</mso:customUI>"
    
    ActiveProject.SetCustomUI (ribbonXml)
End Sub

**Excel Fox** · 07-15-2011, 04:55 PM

**Excel Fox** · 07-16-2011, 08:16 AM

**heapifyman** · 07-18-2011, 07:13 PM

Originally Posted by Excel Fox

heapifyman, as much as I would like to tell there is a method to this using ONLY vba, apparently, there isn't. You could hide/make visible a ribbon component using VBA, but you can't build one in Excel, at least till lately.

If you would like to use the conventional method of adding the xml to the customUI, please feel free to revert.

OK, I guess I'll have to use the standard way then. Do you have a recommendation for a tool to generate/add xml to customUI (including custom images for the buttons)?
Where could I find a good tutorial/documentation for hiding/unhiding a ribbon and activating/deactivating buttons in a ribbon depending on the currently active worksheet?

Thanks in advance.

**Excel Fox** · 07-18-2011, 08:51 PM

For quick and easy development of XML in your workbooks and add-ins, use a

Custom UI Editor

Alternatively, you could also use Andy Pope's RibbonX Visual Designer

**heapifyman** · 07-18-2011, 08:56 PM

Originally Posted by Excel Fox

For quick and easy development of XML in your workbooks and add-ins, use a

Custom UI Editor

Alternatively, you could also use Andy Pope's RibbonX Visual Designer

Unfortunately, your first link does not work: "The resource cannot be found."

However, I found this http://openxmldeveloper.org/cfs-file...ditorSetup.zip

and this http://openxmldeveloper.org/cfs-file...ditorSetup.zip

The first one seems to be newer. Is that a correct assumption? Is there an official version?

**Excel Fox** · 07-18-2011, 09:16 PM

Both the tools that are good in their own ways. Andy's editor does a lot of coding for you, but since I like to customize the way I build up the various components of my ribbon, I often use the first one. Thanks for sharing the link on that one though...