How to set text on Editbox ribbon via VBA (Excel)

Question

How to set text in a ribbon editor? I can not find it on the Internet: /

I just can find examples of click events, but I can't say anything about setting text from Sub.

So, for example, I want something like this:

Sub settingText() editboxname = "my text" end sub 

Answer 1

The solution I found at this link: 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 <?xml version="1.0" encoding="UTF-8"?> <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="OnRibbonLoad"> <ribbon> <tabs> <tab id="Objects" label="Objects"> <group id="grp" label="My Group"> <editBox id="ebCurrentDate" label="Date" onChange="ocCurrentDate" getText="onGetEbCurrentDate"/> </group> </tab> </tabs> </ribbon> </customUI> 

Answer 2

A little since this answer was posted, and looks like a recent change in the behavior of the feed, which means that the original answer may not be the solution anymore. For the record, I use Excel 2013 with some updates dated after Braulio's answer.

The heart of the difference is that Invalidate and InvalidateControl on the tape do not behave the same as before. This means that InvalidateControl does not call the getText callback on the editBox. I replaced the InvalidateControl calls to Invalidate (so that it delays re-drawing on the entire tape), and this triggers the expected response.

So, here is the code of my solution for the filename / browse button (note that I included additional code to cache the link to the ribbon interface on a very hidden sheet to reset it during development to not make the ribbon inaccessible).

 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 

To write here the XML for the two objects I'm dealing with:

 <editBox id="FileName" onChange="OnChangeFilename" screentip="Filename of the XML file to upload" label="XML file name" showImage="false" getText="GetFileNameText" /> <button id="FilenameBrowse" imageMso="ImportExcel" onAction="OnClickFilenameBrowse" screentip="Find the file to upload" label="Browse" />