CODEWITHSUNDEEP
xmlexcelribbonxvba
user3357899
user3357899

Reputation: 11

Excel 2013 ribbonx xml: CustomUI works fine but resulting button refuses to run my macro

Using the customUI tool to add a tab to an Excel 2013 xlsm workbook. Am running Windows 7. CustomUI gives me the green light ("Custom UI XML is well formed!"), and generates callback code. The new tab, new group and new button appears when I open the workbook. I add the callback code to module1 in VBA. But when I click the new button, I get the message

"Cannot run the macro 'AddNodeNew'. The macro may not be available in this workbook or all macros may be disabled."

Well, the macro AddNodeNew IS available, and macros are NOT disabled. So what is going on? Note: The xlsm workbook already contains lots of other macros (none named AddNodeNew).

I wanted to include the code here, but your system won't let me - keeps telling me I have not properly formatted it as code. But I DID indent everything by the 4 spaces it asks for, and still no dice. Help on this topic is inadequate.

Upvotes: 1

Views: 349

Answers (1)

Eleshar
Eleshar

Reputation: 513

Apparently RibbonX custom UI elements can call macros stored in the ThisWorkbook part of the VBA project, but I have not figured out how to call macros from modules, and then I learnt I did not even want to because when a module gets renamed, I would need to adjust the reference in the custom UI.

So what you can do is to create a caller in the ThisWorkbook, which will then call the function. My solution was to put the called function name into the xml element id attribute and then have a single Caller function in ThisWorkbook, which was called by all the custom controls. When called, the Caller reads the control ID attribute and calls another function with that name, regardless of its physical location.

Public Sub Caller(ctrl As IRibbonControl)
    Dim sFunction As String: sFunction = ctrl.ID
    Application.Run sFunction
End Sub

Upvotes: -1

Related Questions