Microsoft Excel Ribbon Cannot run callback macro

Question

I've written many VBA macros over the use but this my first foray into modifying the ribbon with XML and running a macro with ribbon callback. I have followed the instructions for a simple example on Ron de Bruin's webpage precisely, but when I click on the newly created button I get - 'Cannot run the macro "Macro1". The macro may not be available in this workbook or all macros may be disabled." I've tried changing the macro name, specifying with the module name eg "Module1.Macro1" but nothing seems to work.

I use OfficeRibbonXEditor to write the XML which is saved in customUI14.xml in the Book1.xlsm file in the XLSTART folder.

<customUI xmlns="https://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>

      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">

          <button id="customButton1" label="Click Me" size="large" 
        onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>

    </tabs>
  </ribbon>
</customUI>

Validating the XML in the OfficeRibbonXEditor gives -

Custom Ul XML is well formed.

and the generated callback macro is placed in a standard module -

Sub Macro1(control As IRibbonControl)
    MsgBox "Hi There"
End Sub

When Excel 2010 is started the HappyFace icon appears on the ribbon in the right place but clicking on it I get the 'Cannot run the macro...' message. I can't figure this out at all.

This is my first post so I hope I've done things correctly.

Does macro is in the same workook as ribbon? Have you tried to close an open workbook again? Does macro option is enabled in Excel? — Maciej Los, Mar 04 '23 at 17:32
[Enable or disable macros in Microsoft Excel 2010](https://support.microsoft.com/en-us/office/enable-or-disable-macros-in-microsoft-365-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6) — Maciej Los, Mar 04 '23 at 17:39
In my file using CustomUI, there is another lines in the module where your `Sub Macro1(control As IRibbonControl)` reside. Which are : `Public ribRibbon As IRibbonUI` at the top. Then `Public Sub RibbonOnLoad(ribbon As IRibbonUI): Set ribRibbon = ribbon: End Sub`. Do you have that line too ? And on the top of the XML, mine is : `` while in your code, there is no `onLoad = "RibbonOnLoad"` — karma, Mar 05 '23 at 03:31
The Macro1 code is in a standard module - Module1. I've also tried fully qualifyinf the onaction with onAction="Book1.xlsm!Module1.HappyFace" . Macros are enabled as all my non-ribbon macros run without any problems. I have now tried the suggestions from karma, changing the subs and the XML top line but still get the same message now referring to Sub RibbonOnLoad — UkuleleDodger, Mar 05 '23 at 20:37
I'm showing my ignorance here but I'm puzzled. When I look at the list of macros my ribbon macro `Macro1` does not appear. If I remove the `control As IRibbonControl` then it does appear in the list ?? — UkuleleDodger, Mar 06 '23 at 10:27
@Eugene Astafiev I noticed many examples use` ByVal` to reference the control `Sub Macro1( ByVal control As IRibbonControl)` - tried and still get 'Cannot run the macro...' I'm using the Microsoft VBE editor to write VBA code. Is it possible I'm missing one of the references? — UkuleleDodger, Mar 07 '23 at 10:43

score 0 · Answer 1 · answered Mar 05 '23 at 16:27

It seems the ribbon UI (onAction="Macro1") cannot find the callback in a custom module. You need to specify the module name as well, or just define the callback procedure in the default module in VBA.

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.

score 0 · Answer 2 · answered Mar 08 '23 at 15:57

Hi everyone and my apologies. Further searching on the net I found a page with this crucial line “

It works when opened as an .xlsm file but not when loaded automatically from the user XLSTART folder……

When following Ron de Bruin’s example I assumed that when he referred to Book1.xlsm he meant the file in XLSTART – wrong! So I deleted the Book1.xlsm in XLSTART and started afresh, this time using and existing workbook the my documents folder. I created a new customUI14.xml file for the workbook in the Office RibbonX Editor and saved. Started Excel, loaded the workbook and clicked on the Happyface icon and Bingo – all working. So I'm sorry if I wasted your time but at least I've learned something. I'm now goung to attempt to make ribbon changes as an addin so it will be available to all workbooks.

I opened a new workbook, copied/pasted Macro1() into a standard module, saved it as Book1.xlsm, and closed it. Then I used the Office RibbonX Editor to add your XML code. Then I placed the workbook in the user's XLSTART folder. When I opened Excel, Book1.xlsm automatically opened. Then I clicked on the button and the message box appeared, which displayed "Hi There". So it seems to work for me. I'm using Excel 365. — Domenic, Mar 08 '23 at 17:23
@Dominic Interesting. I'm using Excel 2010. I'll check again — UkuleleDodger, Mar 09 '23 at 20:34

Microsoft Excel Ribbon Cannot run callback macro

2 Answers2