CODEWITHSUNDEEP
excelvbacallbackms-officeribbonx
UkuleleDodger
UkuleleDodger

Reputation: 1

Microsoft Excel Ribbon Cannot run callback macro

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.

Upvotes: 0

Views: 989

Answers (2)

UkuleleDodger
UkuleleDodger

Reputation: 1

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.

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

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.

Upvotes: 0

Related Questions