Ribbon Editbox Format Control

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
167
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
[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...


HTML:
<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...


HTML:
<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...


Code:
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...

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

Hope this helps!
 

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
167
Office Version
  1. 2016
Platform
  1. Windows
Many thanks

I am sure that
Code:
returnedVal = "   " & Format(textValue,"0.000"
is going to do it

As always great response...... Paul
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,468
Members
409,883
Latest member
asharris90
Top