如何通过VBA（Excel）在Editbox功能区上设置文本-HowtosetatextonaEditboxRibbonviaVBA(Excel)

9  

The solution I found on this link: http://www.shulerent.com/2011/08/16/changing-the-value-of-an-editbox-office-ribbon-control-at-runtime/

我在这个链接上找到的解决方案：http：//www.shulerent.com/2011/08/16/changing-the-value-of-an-editbox-office-ribbon-control-at-runtime/

Here is an example that I tested and it worked well:

这是我测试的一个例子，它运作良好：

'Global Variables:
Public MyRibbonUI As IRibbonUI
Public GBLtxtCurrentDate As String

Private Sub OnRibbonLoad(ribbonUI As IRibbonUI)

    Set MyRibbOnUI= ribbonUI
    GBLtxtCurrentDate = ""

End Sub

Private Sub ocCurrentDate(control As IRibbonControl, ByRef text)

    GBLtxtCurrentDate = text
    MyRibbonUI.InvalidateControl (control.id)

End Sub

Private Sub onGetEbCurrentDate(control As IRibbonControl, ByRef text)
    text = GBLtxtCurrentDate
End Sub

Public Sub MyTest()
    'Here is an example which you are setting a text to the editbox
    'When you call InvalidateControl it is going to refresh the editbox, when it happen the onGetEbCurrentDate (which is the Gettext) will be called and the text will be atributed.
    GBLtxtCurrentDate = "09/09/2013"
    MyRibbonUI.InvalidateControl ("ebCurrentDate")
End Sub



  
    
      
        
          
        
      
    
  

0  

It's a little while since this answer was posted, and there looks to be a recent-ish change to the behaviour of the ribbon, which means the original answer posted may not be a solution any more. For the record, I'm using Excel 2013 with some updates that are dated after Braulio's answer.

自从这个答案被发布以来已经有一段时间了，并且看起来是功能区的行为的近期变化，这意味着发布的原始答案可能不再是解决方案。为了记录，我正在使用Excel 2013，其中包含一些在Braulio回答后更新的更新。

The heart of the difference is that Invalidate and InvalidateControl on the ribbon don't behave the same way as previously. This means that InvalidateControl does not call the getText callback on the editBox. I replaced the InvalidateControl calls with Invalidate (so forces a re-draw on the entire ribbon), and that does trigger the callback as expected.

区别在于功能区上的Invalidate和InvalidateControl的行为方式与以前不同。这意味着InvalidateControl不会在editBox上调用getText回调。我用Invalidate替换了InvalidateControl调用（因此强制在整个功能区上重新绘制），这确实会按预期触发回调。

So here's the code of my solution for a filename/browse button (note I've included extra code for caching the ribbon UI reference on a very hidden sheet so that resets during development don't make the ribbon inaccessible).

所以这里是我的文件名/浏览按钮解决方案的代码（注意我已经包含额外的代码，用于在非常隐藏的工作表上缓存功能区UI参考，以便在开发过程中重置不会使功能区无法访问）。

Private sobjRibbon As IRibbonUI
Private strFilename As String

Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)

Private Function GetRibbon() As IRibbonUI
    If sobjRibbon Is Nothing Then
        Dim objRibbon As Object
        CopyMemory objRibbon, ThisWorkbook.Worksheets("Ribbon_HACK").Range("A1").Value, 4
        Set sobjRibbon = objRibbon
    End If
    Set GetRibbon = sobjRibbon
End Function

'Callback for customUI.onLoad
Sub Ribbon_Load(ribbon As IRibbonUI)
    Set sobjRibbon = ribbon
    Dim lngRibPtr As Long
    lngRibPtr = ObjPtr(ribbon)
    ' Write pointer to worksheet for safe keeping
    ThisWorkbook.Worksheets("Ribbon_HACK").Range("A1").Value = lngRibPtr
    strFilename = ""
End Sub

'Callback for FileName onChange
Sub OnChangeFilename(control As IRibbonControl, text As String)
    strFilename = text
End Sub

'Callback for FileName getText
Sub GetFileNameText(control As IRibbonControl, ByRef returnedVal)
    returnedVal = strFilename
End Sub

'Callback for FilenameBrowse onAction (I'm looking for XML files here)
Sub OnClickFilenameBrowse(control As IRibbonControl)
    Dim objFileDialog As Office.FileDialog

    Set objFileDialog = Application.FileDialog(msoFileDialogFilePicker)

    With objFileDialog
        .AllowMultiSelect = False
        .Title = "Please select the file."
        .Filters.Clear
        .Filters.Add "XML", "*.xml"

        If .Show = True Then
            strFilename = .SelectedItems(1)
            GetRibbon().Invalidate ' Note the change here, invalidating the entire ribbon not just the individual control
        End If
    End With
End Sub

For the record, here's the XML for the two objects I'm dealing with here:

为了记录，这里是我正在处理的两个对象的XML：