Customizing the Excel Ribbon - intercept IDMSO - VBA

Question

I managed to intercept and control the IDMSO "COPY" / "CUTT" and "PASTE", but I cannot do it for COPY VALEURS / COPY FORMULAS. There are no execution errors, but the IDMSO is not caught, and the substitution code does not execute

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonLoaded">
        <!-- Modife les commandes standard Excel Doit etre placé avant STARTFROMSCRTCH-->   
        <commands>

        <!-- Réattribue l'action du bouton "Insérer un lien hypertexte" -->
        <command idMso="Copy" onAction="MyCopy" />
        <command idMso="Cut" onAction="MyCut" />
        <command idMso="Paste" onAction="MyPaste" />
        <command idMso="PasteValues" onAction="MyPasteValue" />
        <command idMso="PasteValuesAndNumberFormatting" onAction="MyPasteValueF" />
        <command idMso="PasteFormulas" onAction="MyPasteFormula" />
        <command idMso="PasteFormulasAndNumberFormatting" onAction="MyPasteFormula" />

Callbacks. VBA

Sub Mycopy(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlC 'Selection
msgbox "Copy"
End Sub

'*Callback for Cut onAction
Sub Mycut(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlX 'Selection
msgbox "cutt"
End Sub

'*Callback for Paste onAction
Sub MyPaste(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlV 'Selection
msgbox "paste "
End Sub
'*************************************************************************************'
'*************************************************************************************'
'Callback for PasteValues onAction
Sub MyPasteValue(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlV "Valeurs"
msgbox "paste values"
End Sub

'Callback for PasteFormulas onAction
Sub MyPasteFormula(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlV "Formules"
msgbox "paste formulas"
End Sub



'Callback for PasteValuesAndNumberFormatting onAction
Sub MyPasteValueF(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlV "ValeursF"
msgbox "paste values and ft"
End Sub


'Callback for PasteFormulasAndNumberFormatting onAction
Sub MyPasteFormulaF(control As IRibbonControl, ByRef cancelDefault)
'HandleCtrlV "FormulesF"
msgbox "paste vformules and ft"
End Sub

I think that the label of the last 4 IDMSOs are correct (very poorly documented by MS), and I don't understand where the error comes from.

Answer 1

• All idMSO are correct.

• The last 4 commands (bulit-in functions) are functions of the content menu. It is only enabled after users copying something (eg. cells).

• Excel built-in Copy is overwrote by Mycopy(). But it doesn't have coping operation. So the last 4 commands are disabled. It doesn't trigger customed sub (eg. MyPasteFormula()).

• Fix: add cancelDefault = False in all subs. If cancelDefault is set to True, the system cancels the default operation; if set to False, the system proceeds with the default operation.

Sub Mycopy(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "Copy"
    cancelDefault = False
End Sub
Sub Mycut(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "Cut"
    cancelDefault = False
End Sub
Sub MyPaste(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "paste"
    cancelDefault = False
End Sub
Sub MyPasteValue(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "paste values"
    cancelDefault = False
End Sub
Sub MyPasteFormula(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "paste formulas"
    cancelDefault = False
End Sub
Sub MyPasteValueF(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "paste values and ft"
    cancelDefault = False
End Sub
Sub MyPasteFormulaF(control As IRibbonControl, ByRef cancelDefault)
    MsgBox "paste vformules and ft"
    cancelDefault = False
End Sub