Turn selected cells red via custom ribbon button

Question

I am following How to add a custom Ribbon tab using VBA?.

I am attempting by pressing a button in the ribbon named "Leave" to turn all selected cells red.

I created a custom ribbon button using Office RibbonX Editor to the file, and get the pop up "Custom UI XML is well formed".

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
    <tabs>
        <tab idMso="TabAddIns">
        <group id="Leave" label="Leave">
            <button id="LeaveButton" label="Leave" onAction="Leave" 
            imageMso="BeachUmbrella" size="large"/>
        </group>
        </tab>
    </tabs>
    </ribbon>
</customUI>

I added the following code to my Excel document.

Sub Leave()
    MsgBox "Leave button clicked!"
    Selection.Interior.Color = RGB(255, 0, 0)
End Sub

After selecting a few cells, and clicking the button I get:

I am using Professional Plus 2021.

I made numerous files, restarted the computer.

If you use "create VBA-Methods" within Ribbon X Editor you will see that your procedure has to be called `Sub Leave(control As IRibbonControl)` — Ike, Jan 12 '23 at 10:14

score 0 · Answer 1 · answered Jan 12 '23 at 16:56

The callback procedure specified for the onAction attribute in the ribbon XML markup should have the following signature:

C#: void OnAction(IRibbonControl control)
VBA: Sub OnAction(control As IRibbonControl)
C++: HRESULT OnAction([in] IRibbonControl *pControl)
Visual Basic: Sub OnAction(control As IRibbonControl)

So, you need to declare the following code instead:

Sub Leave(control As IRibbonControl)
    MsgBox "Leave button clicked!"
    Selection.Interior.Color = RGB(255, 0, 0)
End Sub

Turn selected cells red via custom ribbon button

1 Answers1