Ribbon Editbox Format Control

Paul Sansom

Board Regular
Joined
Jan 28, 2013
Messages
172
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,235
Office Version
  1. 365
Platform
  1. Windows
[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
172
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,277
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top