Додавання іконки запуску надбудови у стрічку меню Excel 2007-2019

Додати іконку запуску надбудови (Addon) у головну стрічку меню в MS Excel 2007-2019 не можливо простими засобами розробника.

На 

https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba

знайшов працюючий метод.

Що в свою чергу базується на порадах Microsoft з

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/aa338202(v%3Doffice.12)#using-callbacks

Метод наступний:

1. Create Excel file/files whose ribbons you want to customise. In my case, I've created two .xlam files, Chart Tools.xlam and Priveleged UDFs.xlam, to demonstrate how multiple add-ins can interact with the Ribbon.
2. Create a folder, with any folder name, for each file you just created.
3. Inside each of the folders you've created, add a customUI and _rels folder.
4. Inside each customUI folder, create a customUI.xml file. The customUI.xml file details how Excel files interact with the ribbon. Part 2 of the Microsoft guide covers the elements in the customUI.xml file.

My customUI.xml file for Chart Tools.xlam looks like this

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">
  <ribbon>
    <tabs>
      <tab idQ="x:chartToolsTab" label="Chart Tools">
        <group id="relativeChartMovementGroup" label="Relative Chart Movement" >
            <button id="moveChartWithRelativeLinksButton" label="Copy and Move" imageMso="ResultsPaneStartFindAndReplace" onAction="MoveChartWithRelativeLinksCallBack" visible="true" size="normal"/>
            <button id="moveChartToManySheetsWithRelativeLinksButton" label="Copy and Distribute" imageMso="OutlineDemoteToBodyText" onAction="MoveChartToManySheetsWithRelativeLinksCallBack" visible="true" size="normal"/>
        </group >
        <group id="chartDeletionGroup" label="Chart Deletion">
            <button id="deleteAllChartsInWorkbookSharingAnAddressButton" label="Delete Charts" imageMso="CancelRequest" onAction="DeleteAllChartsInWorkbookSharingAnAddressCallBack" visible="true" size="normal"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

My customUI.xml file for Priveleged UDFs.xlam looks like this

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" xmlns:x="sao">
  <ribbon>
    <tabs>
      <tab idQ="x:privelgedUDFsTab" label="Privelged UDFs">
        <group id="privelgedUDFsGroup" label="Toggle" >
            <button id="initialisePrivelegedUDFsButton" label="Activate" imageMso="TagMarkComplete" onAction="InitialisePrivelegedUDFsCallBack" visible="true" size="normal"/>
            <button id="deInitialisePrivelegedUDFsButton" label="De-Activate" imageMso="CancelRequest" onAction="DeInitialisePrivelegedUDFsCallBack" visible="true" size="normal"/>
        </group >
      </tab>
    </tabs>
  </ribbon>
</customUI>

5. For each file you created in Step 1, suffix a .zip to their file name. In my case, I renamed Chart Tools.xlam to Chart Tools.xlam.zip, and Privelged UDFs.xlam to Priveleged UDFs.xlam.zip.
6. Open each .zip file, and navigate to the _rels folder. Copy the .rels file to the _rels folder you created in Step 3. Edit each .rels file with a text editor. From the Microsoft guide

Between the final <Relationship> element and the closing <Relationships> element, add a line that creates a relationship between the document file and the customization file. Ensure that you specify the folder and file names correctly.

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

My .rels file for Chart Tools.xlam looks like this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        <Relationship Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="/customUI/customUI.xml" Id="chartToolsCustomUIRel" />
    </Relationships>

My .rels file for Priveleged UDFs looks like this.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
        <Relationship Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="/customUI/customUI.xml" Id="privelegedUDFsCustomUIRel" />
    </Relationships>

7. Replace the .rels files in each .zip file with the .rels file/files you modified in the previous step.
8. Copy and paste the .customUI folder you created into the home directory of the .zip file/files.
9. Remove the .zip file extension from the Excel files you created.
10. If you've created .xlam files, back in Excel, add them to your Excel add-ins.
11. If applicable, create callbacks in each of your add-ins. In Step 4, there are onAction keywords in my buttons. The onAction keyword indicates that, when the containing element is triggered, the Excel application will trigger the sub-routine encased in quotation marks directly after the onAction keyword. This is known as a callback. In my .xlam files, I have a module called CallBacks where I've included my callback sub-routines.

 

My CallBacks module for Chart Tools.xlam looks like

Option Explicit

Public Sub MoveChartWithRelativeLinksCallBack(ByRef control As IRibbonControl)
  MoveChartWithRelativeLinks
End Sub

Public Sub MoveChartToManySheetsWithRelativeLinksCallBack(ByRef control As IRibbonControl)
  MoveChartToManySheetsWithRelativeLinks
End Sub

Public Sub DeleteAllChartsInWorkbookSharingAnAddressCallBack(ByRef control As IRibbonControl)
  DeleteAllChartsInWorkbookSharingAnAddress
End Sub

My CallBacks module for Priveleged UDFs.xlam looks like

Option Explicit

Public Sub InitialisePrivelegedUDFsCallBack(ByRef control As IRibbonControl)
  ThisWorkbook.InitialisePrivelegedUDFs
End Sub

Public Sub DeInitialisePrivelegedUDFsCallBack(ByRef control As IRibbonControl)
  ThisWorkbook.DeInitialisePrivelegedUDFs
End Sub

Different elements have a different callback sub-routine signature. For buttons, the required sub-routine parameter is ByRef control As IRibbonControl. If you don't conform to the required callback signature, you will receive an error while compiling your VBA project/projects. Part 3 of the Microsoft guide defines all the callback signatures.

---

Перелік, іконок, котрі можна використовуати для стірчки меню, тут

https://bert-toolkit.com/imagemso-list.html