Get name or Id of a ribbon controls

Question

I have a custom ribbon control in my excel that ribbon is developed through 3rd party c# add in. How do I trigger a ribbon button click using VBA code? Many examples over the net show this but my first problem is I even don't know the name of the ribbon. How do I get the name of the ribbon and ribbon button i am interested in?

Answer 1

I found this below link How to get Ribbon custom Tabs IDs?

further reading reached me to this link

http://www.wordarticles.com/Shorts/RibbonVBA/RibbonVBADemo.php

In the download section I downloaded the word template that has a VBA program to read the ribbon,tab and buttons. It also has a code to trigger/ execute the selected button. This VBA function can also be used for excel.

Answer 2

Short answer: You can't do this in Excel
1. I'd suggest you to try CustomUIEditor
2. You may change the extension of excel to .zip and look in the archives for some XML that resembles the buttons. (For example my file has the button "Archivos de Ayuda")

Answer 3

For your first question How do I trigger a ribbon button click using VBA code?

I use the following on the Workbook.Open event that creates the new tab in the ribbon, creates the button with an icon and assigns a Macro to the button. When button pressed it calls a sub from a custom AddIn I created.

Sub RibbonChange()

Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String  

hFile = FreeFile
fileName = "Excel.officeUI"


ribbonXML = "<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>" & vbNewLine
ribbonXML = ribbonXML + "  <mso:ribbon>" & vbNewLine
ribbonXML = ribbonXML + "    <mso:qat/>" & vbNewLine
ribbonXML = ribbonXML + "    <mso:tabs>" & vbNewLine
ribbonXML = ribbonXML + "      <mso:tab id='reportTab' label='YOUR LABLE' insertBeforeQ='mso:TabFormat'>" & vbNewLine
ribbonXML = ribbonXML + "        <mso:group id='reportGroup' label='YOUR LABLE' autoScale='true'>" & vbNewLine
ribbonXML = ribbonXML + "          <mso:button id='runReport' label='YOUR LABLE' " & vbNewLine
ribbonXML = ribbonXML + "imageMso='AppointmentColor2'      onAction='YOUR SUB NAME'/>" & vbNewLine
ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
ribbonXML = ribbonXML + "      </mso:tab>" & vbNewLine
ribbonXML = ribbonXML + "    </mso:tabs>" & vbNewLine
ribbonXML = ribbonXML + "  </mso:ribbon>" & vbNewLine
ribbonXML = ribbonXML + "</mso:customUI>"

ribbonXML = Replace(ribbonXML, """", "")

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

End Sub