Ribbon Editbox Format Control

[Paul Sansom]

Hi
Is there a way to control the format of a Ribbon Editbox.
I want to display values all right aligned and to 3 decimal places?

My Editboxes are populating OK but the responses are all left aligned and no fixed decimal values

Cheers Paul

 

[Domenic]

[Note: Remove the underscore (_) character from each occurrence of on_load in the following code.]

For your XML code, first make sure that you include an on_Load callback for your customUI tag, for example...

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" on_Load="on_Load">

Then, make sure that you add the getText and onChange callbacks for your edit box, for example...

<editBox id="Editbox1" label="Editbox1" getText="Editbox1_getText" onChange="Editbox1_onChange"/>

Then, add the following VBA callback code to a regular module in your workbook...

Private myRibbon As IRibbonUI


Private textValue As String


Public Sub on_Load(ribbon As IRibbonUI)
'
' Code for on_Load callback. Ribbon control customUI
'
    Set myRibbon = ribbon
    textValue = ""
End Sub


Public Sub Editbox1_getText(control As IRibbonControl, ByRef returnedVal)
'
' Code for getText callback. Ribbon control editBox
'
    returnedVal = Format(textValue, "0.000") 'or Format(textValue, "#,##0.000")
End Sub


Public Sub Editbox1_onChange(control As IRibbonControl, Text As String)
'
' Code for onChange callback. Ribbon control editBox
'
    textValue = Text
    
    myRibbon.InvalidateControl control.ID
End Sub

To right-align text, try adding the appropriate spaces before your value. For example, to add 3 spaces before your value...

returnedVal = "   " & Format(textValue, "0.000")

Hope this helps!

 

[Paul Sansom]

Many thanks

I am sure that

returnedVal = "   " & Format(textValue,"0.000"

is going to do it

As always great response...... Paul

 

[Domenic]

You're very welcome!

Cheers!