Excel VBA - Ribbon - Set value of EditBox

Question

I have a custom tab on the excel ribbon with an EditBox. The user should enter a number between 100 and 200 in the EditBox. If the user enters anything other than that, then an error message should pop-up and the text in the EditBox should change to 100. The last part is the one I'm having difficulty with, setting the EditBox text to "100".

'Callback for EditBox onChange event
Sub setQVal(control As IRibbonControl, ByRef text)

    If Not IsNumeric(text) Or text < 100 Or text > 200 Then
        MsgBox "Error! Please enter a value between 100 and 200."
        text = 100   'This doesn't seem to work
        Exit Sub
    End If

    QVal = text

End Sub

Any assistance would be appreciated

user_1603351 · Answer

The signature of the onChange callback should look like this:

Sub OnChange(control As IRibbonControl, text As String)

To change the text you need to implement the getText callback:

Function GetText(control As IRibbonControl) As String

The getText callback is invoked by the Office applications when the Ribbon UI is invalidated. So, you need to force the UI to invoke callbacks. For each of the callbacks that the code implements, the responses are cached.

For example, if an add-in writer implements the getImage callback procedure for a button, the function is called once, the image loads, and then if the image needs to be updated, the cached image is used instead of recalling the procedure. This process remains in place for the control until the add-in signals that the cached values are invalid by using the InvalidateControl method, at which time, the callback procedure is again called and the return response is cached.

In the following example, starting the host application triggers the onLoad event procedure that then calls a procedure that creates an object representing the Ribbon UI. Next, a callback procedure is defined that invalidates a control on the UI and then refreshes the UI.

And in the code, if you need to update the text (get the getText callback invoked) you need to use the following approach:

Dim MyRibbon As IRibbonUI 

Sub MyAddInInitialize(Ribbon As IRibbonUI) 
 Set MyRibbon = Ribbon 
End Sub 

Sub myFunction() 
 MyRibbon.InvalidateControl("editBoxControlID") ' Invalidates the cache of a single control 
End Sub

Excel VBA - Ribbon - Set value of EditBox

1 Answers