Callbacks 3: How to Create the editBox

We've looked at how to use checkBoxes with the Select Case statement. Now, we'll look at the editBox, which allows you to enter anything in the field right in the Ribbon.

Adjust Column Width with the editBox

Sometimes in Excel, I need to finely adjust the width of columns. Hence, I want to define an increment to increase or decrease the column width by. Then, I can click on a button to increase or to decrease the width.

Callback Image

The image on the right shows the editBox I created labeled "Increment" where I enter the increment for the column width. Then, there are two buttons which either increase or decrease the column width by that increment. The XML code for these three controls is:

<editBox id="IncrementBox" label="Increment:" screentip="Set increment to change by" getText="GetText" onChange="OnChange" />
<button id="IncColWidth" image="IncColWidth" label="Inc Col Width" screentip="Increase Column Width by Increment" onAction="RibbonXOnAction" tag="IncreaseColumnWidth" />
<button id="DecColWidth" image="DecColWidth" label="Dec Col Width" screentip="Decrease Column Width by Increment" onAction="RibbonXOnAction" tag="DecreaseColumnWidth" />

The first callback is getText="GetText". As you may have guessed, this callback gets the initial value to display in the increment editBox. It's not necessary for this value to be filled initially; however, if you want a default value, you can specify the initial text. Here is my macro with the initial value of 0.15 passed to Excel Ribbon. The one that applies to this case is Case "IncrementBox". The other cases are for my custom Find and Replace macro.

Public increment As Single

Sub GetText(control As IRibbonControl, ByRef text)
    Select Case control.ID
    Case "IncrementBox"
        text = 0.15 'Omit this line if you don't want to specify initial increment.
        increment = 0.15 'Omit this line if you don't want to specify initial increment.
    Case "FindString"
        FindString = ""
    Case "ReplaceString"
        ReplaceString = ""
    End Select
End Sub

Note: If you omit the two lines above to leave the increment editBox blank at launch, then clicking on the two buttons that increase or decrease the column width will cause an error because increment variable has not been defined.

The text = 0.15 is the initial text that will be displayed in the Increment editBox. The increment = 0.15 assigns initial value to the increment variable for calculation. Next callback is the onChange="OnChange" callback. This tells what to do when the value changes from 0.15 to something else. When the text in the editBox is changed, the increment variable is simply changed with a new value increment = text. Here is my macro:

Sub OnChange(control As IRibbonControl, text As String)
    Select Case control.ID
    Case "IncrementBox"
        increment = text
    Case "FindString"
        FindString = text
    Case "ReplaceString"
        ReplaceString = text
    End Select
End Sub

Lastly, the two buttons call simple macros that increase or decrease the column width by the increment via the onAction="RibbonXOnAction" and the macro name in their tags:

Sub IncreaseColumnWidth()
    CurrentWidth = ActiveCell.ColumnWidth
    ActiveCell.ColumnWidth = CurrentWidth + increment
End Sub

Sub DecreaseColumnWidth()
    CurrentWidth = ActiveCell.ColumnWidth
    ActiveCell.ColumnWidth = CurrentWidth - increment
End Sub

It is important to note that I declared the variable Public increment as Single. This is because the increment value needs to be passed from one subroutine to another.