如何从Excel自定义Ribbon中获取EditBox控件的值？

1. 修正Ribbon XML结构与属性错误 #

你的XML存在两个核心问题：

• 控件嵌套无效：EditBox被放在了Group闭合标签之后，属于非法嵌套，需将其移入<mso:group>内部
• 错误移除引号：Replace(ribbonXML, """", "")会破坏XML语法，属性值必须用引号包裹，这行代码必须删除

修正后的XML生成代码：

Private Sub Workbook_Activate()
    Dim hFile As Long
    Dim path5 As String, fileName10 As String, ribbonXML As String, user As String
    
    hFile = FreeFile
    user = Environ("Username")
    path5 = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    fileName10 = "Excel.officeUI"
    
    ribbonXML = "<mso:customUI xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui' onLoad='onLoad'>" & vbNewLine
    ribbonXML = ribbonXML & "  <mso:ribbon startFromScratch='true'>" & vbNewLine
    ribbonXML = ribbonXML & "    <mso:qat/>" & vbNewLine
    ribbonXML = ribbonXML & "    <mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML & "      <mso:tab id='welcome' label='AMP Cmd Buttons' insertBeforeQ='mso:TabFormat'>" & vbNewLine
    ribbonXML = ribbonXML & "        <mso:group id='AMPgroup' label='ClearData' autoScale='true'>" & vbNewLine
    ribbonXML = ribbonXML & "          <mso:button id='Delete1' label='ClearAll' imageMso='InkEraseMode' onAction='Clear_data'/>" & vbNewLine
    ribbonXML = ribbonXML & "          <mso:editBox id='editBox' getText='GetEditBoxText' label='Location' onChange='EditBoxChanged'/>" & vbNewLine
    ribbonXML = ribbonXML & "        </mso:group>" & vbNewLine
    ribbonXML = ribbonXML & "      </mso:tab>" & vbNewLine
    ribbonXML = ribbonXML & "    </mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML & "  </mso:ribbon>" & vbNewLine
    ribbonXML = ribbonXML & "</mso:customUI>"
    
    Open path5 & fileName10 For Output Access Write As hFile
    Print #hFile, ribbonXML
    Close hFile
End Sub

2. 编写正确的回调函数 #

在标准模块中添加以下代码，回调函数名必须与XML中指定的getText和onChange属性值完全匹配：

' 存储Ribbon对象引用，用于后续控件刷新
Dim ribbonObj As IRibbonUI

' onLoad回调：获取Ribbon对象实例
Sub onLoad(ribbon As IRibbonUI)
    Set ribbonObj = ribbon
End Sub

' getText回调：设置EditBox初始文本
Sub GetEditBoxText(control As IRibbonControl, ByRef text)
    text = "默认位置" ' 可自定义初始显示内容
End Sub

' onChange回调：实时获取EditBox输入值
Sub EditBoxChanged(control As IRibbonControl, text As String)
    MsgBox "输入的位置是：" & text
End Sub

' 按钮的onAction回调（保留原有功能）
Sub Clear_data(control As IRibbonControl)
    ' 此处添加清除数据的逻辑代码
    MsgBox "执行清除数据操作"
End Sub

3. 生效步骤 #

1. 保存并关闭Excel
2. 重新打开包含上述代码的工作簿，激活时会自动生成新的Excel.officeUI配置文件
3. 在Ribbon的「AMP Cmd Buttons」标签下，编辑「Location」输入框，输入内容后会触发弹窗显示输入值

关键注意事项 #

• XML属性值必须用引号包裹，禁止移除引号的操作
• 回调函数签名必须严格匹配规范：onChange回调固定参数为(control As IRibbonControl, text As String)
• 所有控件必须嵌套在<mso:group>内部，遵循Ribbon XML结构规范

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