customize users ribbon (like in excel options) upon open/activation

Hello,



I know I can customize my ribbon through the excel options, but how to I do it programatically? I was hoping to find some code to add to the workbook_open, workbook_activation events to do the following:

Upon open or activation the workbook - hide all the usual Excel ribbon areas and show only the special one that was made for said workbook.

Upon close or deactivation of this same workbook - let the ribbon return to the normal state.

Thank you

Welcome to the world of XML: http://www.rondebruin.nl/win/section2.htm
You can also use Andy's add-in from here: http://www.andypope.info/vba/ribboneditor_2010.htm

I edited the XML to make the ribbon in the first place - I was hoping the solution was a bit more straightforward than using another add-in.

I mean, to make the ribbon appear you only have to:

Change the workbook file name to end in .zip and extract the contents,
Edit the file in the _rels directory to include this tag:

<Relationship Id="someID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

Make a new directory in the root directory you extracted the contents too named customUI, and a place a file named customUI.xml (this just matches the path identified as Target in the tag you add to the rels file)

For my own project, the customUI file just reads:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomTab" label="SNB Report">
<group id="SampleGroup" label="Comment Tasks">
<button id="Button3" label="Sync Comments" imageMso="AccessRefreshAllLists" size="large" onAction="ribbonControl_runSyncWithServer" />
</group >
<group id="SNB_admin" label="Admin Tasks">
<button id="Button4" label="Upload SAP Data Set" imageMso="ViewsAdpDiagramSqlView" size="large" onAction="Upload_Latest_SAP" />
</group>
<group id="Table_Sort" label="Admin Tasks">
<button id="Button5" label="Sort" imageMso="SortFilterMenu" size="large" onAction="sort_query_table" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>



Does someone know which xml file to edit to set some kind of "don't show this tab" option for the rest of the ribbon?


Thank you romperstomper - i may just have to use the add in but would really like to just understand the proccess a bit more.

All you need is the startfromscratch attribute in the CustomUI.xml - I'm pretty sure Ron covers this.

Well, I wasn't able to find it on Ron's site.

But thank you for naming the attribute, after googling "startFromScratch" I found a good link: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

All I needed to change was the one tag:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="CustomTab" label="SNB Report">
<group id="SampleGroup" label="Comment Tasks">
<button id="Button3" label="Sync Comments" imageMso="AccessRefreshAllLists" size="large" onAction="ribbonControl_runSyncWithServer" />
</group >
<group id="SNB_admin" label="Admin Tasks">
<button id="Button4" label="Upload SAP Data Set" imageMso="ViewsAdpDiagramSqlView" size="large" onAction="Upload_Latest_SAP" />
</group>
<group id="Table_Sort" label="Admin Tasks">
<button id="Button5" label="Sort" imageMso="SortFilterMenu" size="large" onAction="sort_query_table" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>