Load a unique custom ribbon automatic per Excel spreadsheet on startup-CodePudding

I have various spreadsheets in different dirs that all needs to load their own custom ribbons on start-up.

All of these xls sheets have the same basic custom ribbon structure/icons/group names/macros/etc except some of the macros in the spreadsheets are different. The path in some of the macros refer to a config file (txt) that that changes according to the Excel file name.

I have read searched/read up and tried so many ways to make this work but I just can’t seem to crack it.

One main problem is, is that I seem to mis is that the code referring to the macros should not be in the same “Custom ribbon xml” file. I can’t find any clear solution to this. The only indicator I can find is that referrals to the macros should be a separate Module in Excel file/s.

One of the issues I get (due to my lack of xml knowledge) is the “Unknown namespace "".” error I get when validating the xml file in Office RibbonX Editor I don’t understand. Ln 1, Col 1: Unknown namespace "". Custom UI XML namespace must be "http://schemas.microsoft.com/office/2009/07/customui"

I’ve been at this for two days now but can’t seem to crack it. With limited xml knowledge I wanted to sort this myself because then I’ll know how it all works.

Followed some of the instructions in links below in many others on the internet. https://www.rondebruin.nl/win/s2/win002.htm https://gregmaxey.com/word_tip_pages/customize_ribbon_main.html

Using application Office RibbonX Editor: https://github.com/fernandreu/office-ribbonx-editor

Posted below is the original export UI file from Excel. Just so this so there is no confusion about changes made.

<mso:customUI xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
    <mso:qat/>
    <mso:tabs>
        <mso:tab idQ="mso:HelpTab" visible="false"/>
        <mso:tab id="mso_c1.15050BD" label="ITTA">
            <mso:group id="mso_c1.151240B" label="CONFIG" imageMso="RecordsRefreshMenu" autoScale="true">
                <mso:button idQ="x1:D:_Temp_GTS_Temp_GTS.xlsm_All_config_files_0_15D3670" label="Update Config" imageMso="AutoFormat" onAction="D:\Temp\GTS\Temp\GTS.xlsm!All_config_files" visible="true"/>
            </mso:group>
            <mso:group id="mso_c1.1580F39" label="DATA Sheet" autoScale="true">
                <mso:button idQ="x1:D:_Temp_GTS_Temp_GTS.xlsm_Import_CSV_Data_file_0_15C570C" label="Import_CSV_Data_file" imageMso="SmartArtLayoutGallery" onAction="D:\Temp\GTS\Temp\GTS.xlsm!Import_CSV_Data_file" visible="true"/>
                <mso:button idQ="x1:D:_Temp_GTS_Temp_GTS.xlsm_Calculate_Stats_1_15C570C" label="Calculate_Stats" imageMso="OutlineSubtotals" onAction="D:\Temp\GTS\Temp\GTS.xlsm!Calculate_Stats" visible="true"/>
                <mso:button idQ="x1:D:_Temp_GTS_Temp_GTS.xlsm_Format_Columns_DATA_Sheet_2_15C570C" label="Format L ayout" imageMso="HorizontalSpacingDecrease" onAction="D:\Temp\GTS\Temp\GTS.xlsm!Format_Columns_DATA_Sheet" visible="true"/>
                <mso:button idQ="x1:D:_Temp_GTS_Temp_GTS.xlsm_Sort_by_Date_and_ID_3_15C570C" label="Sort Date &amp;&amp; ID" imageMso="_3DPerspectiveDecrease" onAction="D:\Temp\GTS\Temp\GTS.xlsm!Sort_by_Date_and_ID" visible="true"/>
                <mso:button idQ="x1:D:_Temp_GTS.xlsm_Sort_by_Seq_Date_4_15C570C" label="Sort Seq &amp;&amp; Date" imageMso="_3DPerspectiveIncrease" onAction="D:\Temp\GTS\Temp\GTS.xlsm!Sort_by_Seq_Date" visible="true"/>
            </mso:group>
            <mso:group id="mso_c5.15B8EEA" label="SINGLE Sheet" autoScale="true">
                <mso:button idQ="x1:D:_Temp_GTS_Temp_GTS.xlsm_SingleSheet_Format_5_15C570C" label="Format layout &amp;&amp; Sort" imageMso="HorizontalSpacingIncrease" onAction="D:\Temp\GTS\Temp\GTS.xlsm!SingleSheet_Format" visible="true"/>
            </mso:group>
        </mso:tab>
    </mso:tabs>
</mso:ribbon>

</mso:customUI>

CodePudding user response：

One of the issues I get (due to my lack of xml knowledge) is the “Unknown namespace "".” error

Take a look at the x1 namespace in the ribbon XML markup and moreover you will find that the file has two declarations:

<mso:customUI xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">

where

xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro"

is not required at all. You simply could use the following:

<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">

When you don't know what could be the problem with a custom ribbon UI in Office applications or why it is not displayed, I'd suggest enabling ribbon UI errors in the host application. By default, if an add-in attempts to manipulate the Microsoft Office user interface (UI) and fails, no error message is displayed. However, you can configure Microsoft Office applications to display messages for errors that relate to the UI. You can use these messages to help determine why a custom ribbon does not appear, or why a ribbon appears but no controls appear. See How to: Show Add-in user interface errors for more information.

You can read more about the possible ribbon XML markup in the following articles: