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