Custimising Ribbon

[GregoryGoon]

My machine uses Windows 10 and Excel 2019. I am aiming to add a dozen or so custom buttons to the Ribbon to invoke some macros that have been tried and tested on Excel 2000.

As simple trial of the proposed system, I have written some XML to add   a custom tab to the ribbon with one group containing one button with a custom icon

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<tabs>

<tab id = "first_tab" label = "Our First Tab">

     <group id = "group1" label = "First Group">

<button id = "button1" label = "Hello World" image = "RedButton9731" size="large" onAction = "HelloWorld_2"/>

     </group>

</tab>

</tabs>

</ribbon>

</customUI>

There are various .rels files of course.

I have written two simple macros, stored in Module1 of the Personal.xlsb

Sub HelloWorld_1()

   MsgBox "Hello World One"

End Sub

Sub HelloWorld_2(control1234 As IRibbonControl)

   MsgBox "Hello World Two"

End Sub

The Project Explorer shows that they are available to my Workbook. All looks OK, with the tab, group, button, and icon showing, but the callback does not work.

Macro 1 works well when invoked using Developer -> Macros -> Run.

However, clicking button1 gives the message:
“Cannot run the macro 'HelloWorld_2. The macro may not be available in this workbook or all macros may be disabled.”

Can anybody help please.?

[#2 · Accepted solution]

@GregoryGoon , Hi, i think you need to write full address of the macro in the xml.

Something like :-

onAction="'C:\....path of personal.xlsb.....\Personal.xlsb'!HelloWorld_2"

Most possibly, the complete path is as below :-

onAction="'C:\Users\"user name"\AppData\Roaming\Microsoft\Excel\XLStart\Personal.xlsb'!HelloWorld_2"

where user name is your windows account name