Calling an excel macro from the ribbon

Question

Intro: I have written some short excel macros (tested, they work fine) and want to link them to a button in the Ribbon (Excel 2010). I had already done it successfully in Excel 2007. I am using Custom UI Editor to build a new ribbon, which also works fine. Everything is packaged in a .xlam add-in and added to Excel. The ribbon shows up nicely, all other buttons works, but ...

Problem: when I hit the button that is linked to the macro I get the error: "wrong number of parameters or property assignment not valid" (message translated from Italian, might not be exactly the same in English)

Troubleshooting info: The macros do not have parameters. The same macros can be successfully called and executed manually. I am even able to add the same macros to the Quick Access Toolbar.

Here is the specific portion of the ribbon script:

<group id="DupNumber" label="Number" insertBeforeMso="GroupNumber" >  
    <comboBox idMso="NumberFormatGallery"/> 
    <box id="HN1" boxStyle="horizontal"> 
        <buttonGroup id="HNButtonGroup1"> 
            <button id="Euro" onAction="Roberto.xlam!EURZ" imageMso="F" supertip="text ..."/> 
            <button id="EuroNZ" onAction="Roberto.xlam!EURNZ" imageMso="E" supertip="text ..."/> 
            <button idMso="PercentStyle"/> 
            <button id="Comma" onAction="Roberto.xlam!NewCommaFormat" imageMso="C" supertip="test ..."/> 
            <button idMso="PercentStyle"/> 
        </buttonGroup> 
    </box>

and here are the macros:

Sub EURZ()
    Application.ActiveCell.NumberFormat = "€ #,##0.00"
End Sub
Sub EURNZ()
    Application.ActiveCell.NumberFormat = "€ #,##0"
End Sub
Sub NewCommaFormat()
    Application.ActiveCell.NumberFormat = "#,##0"
End Sub

Can you help me? Thanks Roberto

score 12 · Accepted Answer · edited Nov 15 '21 at 16:26

12

I believe you need to add this param to your macro: control As IRibbonControl

So it should look like this:

Sub EURZ(control As IRibbonControl)
    Application.ActiveCell.NumberFormat = "€ #,##0.00"
End Sub

edited Nov 15 '21 at 16:26

BryanH

5,826
3
34
47

answered Aug 18 '11 at 15:27

Justin Self

6,137
3
33
48

3

You may also want to set this argument as Optional so you can keep running the sub from the debugger for example. – Thierry Dalon Aug 10 '16 at 09:03
1

@Thierry Dalon Even though the argument is marked as optional, I no longer see the Sub under the macro listing. I can activate the Sub via the Ribbon though. – TechFanDan Jul 08 '19 at 13:56

Calling an excel macro from the ribbon

1 Answers1

Linked