Excel功能区自定义：IDMSO拦截失效问题咨询（VBA）

我通过VBA成功拦截并控制了IDMSO的「Copy」「Cut」「Paste」命令，但无法拦截「粘贴值」「粘贴公式」这类相关命令。程序无执行错误，但IDMSO命令未被捕获，自定义回调代码不生效。我认为最后4个IDMSO标签正确（微软相关文档极少），却不知问题出在哪。

原XML代码：

<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" />

原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

问题分析与修正方案 #

1. IDMSO名称错误 #

微软官方标准IDMSO名称中，PasteValuesAndNumberFormatting、PasteFormulasAndNumberFormatting是错误的，正确名称应为PasteValuesAndNumberFormats和PasteFormulasAndNumberFormats（后缀为Formats而非Formatting）。

2. 回调函数名称不匹配 #

XML中最后一条命令PasteFormulasAndNumberFormatting的onAction指向MyPasteFormula，但对应的VBA回调函数是MyPasteFormulaF，名称不一致导致回调无法触发。

3. XML标签未闭合 #

原XML代码缺少</commands>和</customUI>闭合标签，会导致Ribbon加载异常，部分命令无法被识别。

修正后的代码 #

修正后的XML代码 #

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonLoaded">
    <!-- 修改Excel标准命令，需放在STARTFROMSCRTCH之前 -->   
    <commands>
        <!-- 重新定义"插入超链接"按钮的动作 -->
        <command idMso="Copy" onAction="MyCopy" />
        <command idMso="Cut" onAction="MyCut" />
        <command idMso="Paste" onAction="MyPaste" />
        <command idMso="PasteValues" onAction="MyPasteValue" />
        <command idMso="PasteValuesAndNumberFormats" onAction="MyPasteValueF" />
        <command idMso="PasteFormulas" onAction="MyPasteFormula" />
        <command idMso="PasteFormulasAndNumberFormats" onAction="MyPasteFormulaF" />
    </commands>
</customUI>

修正后的VBA代码（仅调整名称一致性，逻辑不变） #

Sub MyCopy(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlC 'Selection
    MsgBox "Copy"
    cancelDefault = True ' 可选：取消默认操作，根据需求调整
End Sub

'*Callback for Cut onAction
Sub MyCut(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlX 'Selection
    MsgBox "Cut"
    cancelDefault = True
End Sub

'*Callback for Paste onAction
Sub MyPaste(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlV 'Selection
    MsgBox "Paste"
    cancelDefault = True
End Sub

'Callback for PasteValues onAction
Sub MyPasteValue(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlV "Valeurs"
    MsgBox "Paste Values"
    cancelDefault = True
End Sub

'Callback for PasteFormulas onAction
Sub MyPasteFormula(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlV "Formules"
    MsgBox "Paste Formulas"
    cancelDefault = True
End Sub

'Callback for PasteValuesAndNumberFormats onAction
Sub MyPasteValueF(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlV "ValeursF"
    MsgBox "Paste Values and Number Formats"
    cancelDefault = True
End Sub

'Callback for PasteFormulasAndNumberFormats onAction
Sub MyPasteFormulaF(control As IRibbonControl, ByRef cancelDefault)
    'HandleCtrlV "FormulesF"
    MsgBox "Paste Formulas and Number Formats"
    cancelDefault = True
End Sub

注：添加cancelDefault = True可以取消Excel默认的粘贴操作，若需要保留默认操作可移除该行。

内容的提问来源于stack exchange，提问作者phmauber